| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2005-02-04 : 21:14:43
|
| Hello, I am designing a database backend to an online RPG I am making. One of the things is that players can go and buy items from shops. My question is what the best way to do this is, should I make one table that has a coloum for each shop that would contain the QTY amount that each planet has (the table would also contian a colum that has the price the item sells for and the name of the item) OR should I make a seperate table for each planet that just contains the item name, QTY amount, and sell price?--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-04 : 23:16:59
|
| Planet, item, quantity, priceYou don't want to add new tables every time you add a planet.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-02-05 : 00:07:52
|
| nr, i think your missing part of it though, because Im thinking Eagle wants to have a seperate quantity for each planet?Select * from users where clue > 0 |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2005-02-05 : 07:54:56
|
quote:
Originally posted by Auric
nr, i think your missing part of it though, because Im thinking Eagle wants to have a seperate quantity for each planet?Select * from users where clue > 0
Correct, each planent can have the same item but in differant amounts.But I think I get what NR is saying..Do you mean have an entry for each item for each planet? |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-02-06 : 09:49:59
|
| Are the prices the same for each item from planet to planet? and are you tracking the quantity of each item for each planet? Basic point of sale apps usually have 1 table for each vendor (planet), one for item numbers (this can have your prices if they are always the same), and one for pricing (if the prices are different). The item table holds all of the pertinent info about the item, while the planet table holds WHICH items are stocked there. If you go one step further, and have a table to define each planet, then you can define which vendors are available at which planet at any given time (close a shop for the night on one planet but not another). This can get carried away REAL quick!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-06 : 10:11:07
|
| >> Basic point of sale apps usually have 1 table for each vendor (planet).Really?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2005-02-06 : 10:23:35
|
quote:
Originally posted by steamngn
Are the prices the same for each item from planet to planet? and are you tracking the quantity of each item for each planet? Basic point of sale apps usually have 1 table for each vendor (planet), one for item numbers (this can have your prices if they are always the same), and one for pricing (if the prices are different). The item table holds all of the pertinent info about the item, while the planet table holds WHICH items are stocked there. If you go one step further, and have a table to define each planet, then you can define which vendors are available at which planet at any given time (close a shop for the night on one planet but not another). This can get carried away REAL quick!AndyThere's never enough time to type code right, but always enough time for a hotfix...
All item prices will be the same on each planet, only the QTY for sell will be differant. Technicly each planet will sell all the items in the game but some planets start with 0 QTY. I am really likeing your idea of mutli-tables as for ease of update. Just so I make sure I have it right there is one table that contains ItemName and Price and then each planet gets it's on table with ItemName and QTY?--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-06 : 11:03:16
|
| Go with what you feel comfortable with but I suspect that will be causing work for yourself.Also do you always want to have the same price on all planets? It might be worthwhile catering for different prices.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2005-02-06 : 12:51:09
|
quote:
Originally posted by nr
Go with what you feel comfortable with but I suspect that will be causing work for yourself.Also do you always want to have the same price on all planets? It might be worthwhile catering for different prices.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Like I said in the my orignal post this is for an online game so prices will not be changed. I am just looking for the easiest table design so I use a T-SQL script for users to buy and sell items to the shop, which was why I though maybe one table orignaly. I am fully willing to lissen to any and all suggestions any one has on how to make the tables.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-06 : 13:12:01
|
| See my original post. If you are sure you want a global price then tablesPlanet, item, quantityitem, price==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-02-06 : 20:53:26
|
| hey NR,AAhhhhhh...... I never said I could type! I meant to say "Basic point of sale apps have one table for vendors(planets)" Not one table for EACH vendor (Man would that be overkill!)Your hierarchy is right on, I was just trying to help clarify the information for Eagle_f90.AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
lori.bolen
Starting Member
1 Post |
Posted - 2005-02-22 : 15:34:08
|
| What if you had a case like this: You wanted to keep take of miles traveled in each state, fuel purchased in each state, the total cost of the fuel purchased for each state and this information was to be kept for each load. Would it be better to have one huge table - one entry in the table per load with 150 fields. Or would it be better to have one table per state and the table contained the load, miles, gallons and cost with load being the primary key. This way would result in 50 tables with the same structure for each state. Or is there a better way?lori |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-22 : 17:56:15
|
quote:
Originally posted by lori.bolen
What if you had a case like this: You wanted to keep take of miles traveled in each state, fuel purchased in each state, the total cost of the fuel purchased for each state and this information was to be kept for each load. Would it be better to have one huge table - one entry in the table per load with 150 fields. Or would it be better to have one table per state and the table contained the load, miles, gallons and cost with load being the primary key. This way would result in 50 tables with the same structure for each state. Or is there a better way?lori
Start a new thread if you have a specific issue that you want answered.Having 1 table per state does not make sense. Logically its just 1 big table. Physically You could implement it as a partitioned view, (The view is logically the 1 big table, physically it is split into 1 table per state).rockmoose |
|
|
|
|
Should I do one table or many tables?