user3050832 user3050832 - 5 months ago 11
SQL Question

Normalising a database to reduce dependency

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...


Seems this is bad design considering repetitions for category and brand will occur. Thus I split it into 3 tables as follows:

Category Table

id name(pk)

1 Beer
2 Liquor


Brand Table

id name(pk) category_name(FK)
1 Heineken Beer
2 Carlsburg Beer
3 Lindemans Wine
4 JWalker Liquor


Product Table

id(PK) type price quantity description category_name(FK) brand_name(FK)
1 Bottle $2.00 100 some description Beer Heineken


Thought this would be better normalised but the way I see it, hardly a difference from the first table. And I end with type repeatnig too since I can get repetitions on bottle, can and so on. So should I get a 4th table for that?

Trying to normalise and keep it as sensible as possible. Is there a better way to go about doing this?

Answer

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
Comments