Database design,multi-key or single-key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database design,multi-key or single-key

Author

Topic

lolmuta
Starting Member

3 Posts

Posted - 2014-02-20 : 09:06:53

I am a DB noob.i often be a user,not a builder.

I want to build a Form beverage .

here is the form Which Include the store keys, beverage key

Suppose there is a the store which store key = A
,and this store Sell green-tea ,red-tea,milk-tea,
....

and beverage key will be like this
123 ....

now
Added a new the store , the store key = B
and it Sell Cappuccino ,Americano,Caffè Latte,...
Then I beverages the key will be like ?
123 ....
Or
456 ....

This is 123
store_id product_id product_name
A 1 green-tea
A 2 red-tea
A 3 milk-tea
B 1 Cappuccino
B 2 Americano
B 3 Caffè Latte

This is 456
store_id product_id product_name
A 1 green-tea
A 2 red-tea
A 3 milk-tea
B 4 Cappuccino
B 5 Americano
B 6 Caffè Latte

If to 123, then I need two keys (the store keys3, _ beverage key) in order to determine the product
If to 456, then I just one key (beverage key) will be able to determine the product

which one this better?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-20 : 11:44:16

classic way is to have 3 tables. Something like this:


- Stores (storeid, storename, etc.)       PK: storeid
- products (productid, productname, etc.) PK: productID
- StoreProduct: (storeid, productid)      PK: storeid, productid

Your StoreProduct could also be an [inventory] table unless you need separate inventories by store.

So your first example "123" makes more sense to me. Cappuccino is only "3". Not different IDs depending on what store it is in.

Be One with the Optimizer
TG

lolmuta
Starting Member

3 Posts

Posted - 2014-02-20 : 20:10:43

i want to make order system.
user can order many stores many beverages.
different store sell the same beverage,but they are not the same price.even taste not the same...

if store A sell Green-tea taste like X and price is 2
if store C sell Green-tea taste like Y and price is 2.5
and this

- products (productid, productname, etc.) PK: productID
if follow this form,u will get this result below
product id,productname,taste,price
1 green-tea x 2
2 green-tea y 2.5

this is very strange,why same product-name has so many different data...
i think this is not a good idea to get
- products (productid, productname, etc.) PK: productID

i would choose 2 form like this.
- Stores (storeid, storename, etc.) PK: storeid
- StoreProduct: (storeid, productid) PK: storeid, productid

let back to the original problem, the red one form

PK(1 key)=productid or
PK(2key)=storeid, productid ?
which is better

lolmuta
Starting Member

3 Posts

Posted - 2014-02-20 : 21:56:56

delete or add or determind which product it is,
mutli-key is more hard to handle.
single-key,my code will easier to write code...

i will choose single key in this case

store_id product_id product_name
A 1 green-tea
A 2 red-tea
A 3 milk-tea
B 4 Cappuccino
B 5 Americano
B 6 Caffè Latte

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources