I am trying to get my database design right. It is a large set of alcohol drinks consisting of beer, liquor, wine and so on. I could keep it all in a single table as follows:
id category brand type price quantity description
1 Beer Heineken bottle $2.00 100 some description...
2 Beer Calsburg bottle $3.00 200 some description
3 Beer Heineken can $1.00 300 some description....
4 Liquor JWalker bottle $30.00 100 some descri...
id name(pk) category_name(FK)
1 Heineken Beer
2 Carlsburg Beer
3 Lindemans Wine
4 JWalker Liquor
id(PK) type price quantity description category_name(FK) brand_name(FK)
1 Bottle $2.00 100 some description Beer Heineken
Table creation would look something like this:
create table product ( product_id int not null identity, brand_id int not null, category_id int not null, primary key(product_id), foreign key brand_id references brand(brand_id), foreign key category_id references category(category_id) ); create table brand ( brand_id int not null identity, name varchar(80), primary key(brand_id) ); create table category ( category_id int int not null identity, name varchar(80), primary key(category_id) );
You do a JOIN to get the record back:
select p.product_id, c.name as category_name, b.name as brand_name from product as p join category as c on p.category_id = c.category_id join brand as b on p.brand_id = b.brand_id