BCNF table design multiple values

Question

I'm designing a relational database for products in a database. Here's a general outline

Product(Name, Id, Category, Price, Location...)

What I'm stuck on is that each product can have multiple categories. How do I deal with this is a BCNF design? I was thinking of making a separate table to map Id -> Categories, but there's no functional dependency here since each Id can have multiple categories...

Can someone explain to me what the functional dependency is here on category and how to resolve it in a BCNF design?

I think you might be dealing with a multi-valued dependency, meaning you may need to go beyond BCNF to 4NF to sort it all out. — NealB

Kiley Naro Kiley Naro · Accepted Answer · 2011-10-20T18:19:11

From: http://www.google.com/url?sa=t&rct=j&q=multivalued%20attributes%20in%20relational%20tables&source=web&cd=2&ved=0CCYQFjAB&url=http%3A%2F%2Fwww.cs.sjsu.edu%2Ffaculty%2Flee%2Fcs157%2F26Presentation_Jung_T_Chang.ppt&ei=f2SgTsr3NqnA0AGo2dCeBQ&usg=AFQjCNH_RoewSGJRuT4KOMtudGnX0uMimw&cad=rja (How to translate ER Model to Relational Model by By: Jung T. Chang):

For each multivalue attribute in an entity set/relationship set:
- Build a new relation schema with two columns
- One column for the primary keys of the entity set/relationship set that has the multivalue attribute
- Another column for the multivalue attributes.  Each cell of this column holds only one value.  So each value is represented as an unique tuple 
- Primary key for this schema is the union of all attributes

An example from his powerpoint: (I tried uploading a screen shot from his power point but imgurl isn't working for me right now.... check out slide #19 from the link above to see an example)

In your case, it depends on the relationship between Product and Category.

If it's a many-to-one relationship (meaning the categories are unique for each product but each product has multiple): create a Categories table, with a foreign key pointing to the Product table.

If it's a many-to-many relationship (meaning one Product can have many Categories and any single Category can belong to multiple Products, an "Associative Entity" (http://en.wikipedia.org/wiki/Associative_Entities) is required. This is basically a simple "map" table which has a foreign key to the Product table, and a foreign key to the Categories table, and the combination of the two creates a composite primary key (http://en.wikipedia.org/wiki/Compound_key).

Edit

You mentioned in your comment that Category is a multi-valued attribute of Product, meaning that for a single product there exists multiple categories that the product belongs to. With any multi-valued attribute, you are going to need another table to represent the data. The only question left is to ask whether it is a One-to-many relationship between Product and Category, or a many-to-many relationship. You have already confirmed that it is many-to-many, meaning that a single product can have multiple categories, and a single category can have multiple products, so this question is answered.

In order to develop a solution, you need three tables:

Product(Name, Id, Price, Location...)

ProductCategory(ProductID, CategoryID)

Category(Name, Id...)

The Id field in both tables in your primary key. This is still BCNF because all of your attributes in Product and Category tables are dependent upon the Id key fields, and A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.. The primary key in the ProductCategory table is the composite key of ProductID and CategoryID. You don't need a functional dependency in the Products table that mentions category in order for you to achieve BCNF; in fact, you probably shouldn't because the two entities are logically distinct in this case.

BCNF table design multiple values

2 Answers

Edit