SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Database design,multi-key or single-key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lolmuta
Starting Member

3 Posts

Posted - 02/20/2014 :  09:06:53  Show Profile  Reply with Quote
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?



Edited by - lolmuta on 02/20/2014 09:53:32

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 02/20/2014 :  11:44:16  Show Profile  Reply with Quote
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
Go to Top of Page

lolmuta
Starting Member

3 Posts

Posted - 02/20/2014 :  20:10:43  Show Profile  Reply with Quote
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

Edited by - lolmuta on 02/20/2014 20:16:32
Go to Top of Page

lolmuta
Starting Member

3 Posts

Posted - 02/20/2014 :  21:56:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000