Normalising a database to reduce dependency

Question

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 1

Brand Table
brandID(PK) BrandName

Category table
BrandID(FK) CategoryID(PK) Categoryname

Product table
ProductID(PK) CategoryID(FK) description price quantity

Answer 2

Normalization requires knowing functional dependencies (FDs) and join dependencies (JDs) that hold. You haven't given them. So we can't normalize. But guessing at your application and your table, it is in 5NF.

Presumably id is a unique column. So it functionally determines every column set. Since no smaller subset of {id} is unique, it is a candidate key (CK). Presumably no other FDs hold other than the ones that hold because of that CK. So the table is in 5NF.

But suppose also one more FD holds: that a given brand only ever appears with the same category. Then to normalize to 5NF column category should dropped and a new table should be added with brand & category columns and CK {brand}.

Or suppose that a brand has one or more categories, and instead of a row stating that category is its product's category, it states that category is a category of its product's brand. (Weird, since then for brands with more than one category the table wouldn't give a product's category.) Then normalization also gives those two tables, with new CK {category, brand}. But in this case it's because of a multi-valued dependency (MVD), ie because of a binary JD.

PS Introducing ids has nothing to do with normalization.

PPS You seem to think that repeated subrow values imply a need for normalization. They don't. Normalization is for sometimes replacing a table by tables that always join to it.

Answer 3

Normalization through BCNF is based on functional dependencies. It's not based on whether a column contains text or numbers. You seem to think that, because the category column contains the word Beer more than once, it needs to be "normalized". That's not the case.

So what are the functional dependencies here?

• id -> category, brand, type, price, quantity, description
• category, brand, type, -> id, price, quantity, description

That second FD might be wrong. It might be that {brand, type} is the determinant. But I think it's likely that there's a company somewhere that makes both beer and liquor under the same brand name. So I think that the determinant is probably {category, brand, type}.

That's in 5NF already. "Splitting" isn't going to improve this table.

Answer 4

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