Null Foreign Key

views:

answers:

+2 Q:

Hello, newbie programmer here.

I have 3 tables namely product, category, and subcategory. I configured their relationships this way:

Product to Category: Many-to-many
Product to Subcategory: One-to-one
Subcategory to Category: Many-to-one

I added a subcategory_id column which is a foreign key in the product table (for mapping the product and subcategory tables). This works if a product has a subcategory. Now the problem is I have products which doesn't have subcategory. Supposed to be the subcategory_id column will be null, but it's not allowed. Is there a workaround for this? I also think that the relationship configuration has something to do with this. Any thoughts?

+3 A:

As long as the subcategory_id is nullable, you should be able to add a foreign key that will enforce the integrity of the relationship in all cases where the column is not null. This is a fairly common use case.

Asaph 2009-12-17 03:38:08

Thanks for the quick answer sir."you should be able to add a foreign key that will enforce the integrity of the relationship in all cases where the column is not null" - Please enlighten me more on this, I didn't catched it quite well.

Ed 2009-12-17 03:39:39

Ed, he means that whenever the field is not null, the value of that field is enforced as a valid foreign key to another table/record.

Tony k 2009-12-17 03:51:55

Product to Category: Many-to-many

Product to Subcategory: One-to-one

Subcategory to Category: Many-to-one

That doesn't make sense. If product and subcategory are one to one, then they are the same entity. Or are they one to 0/1 ?

In any event, either way, If they're one to one or one to zero or one, then every product is from a different subcategory, and every subcategory has at most one product assigne to it. If this is true then it cannot be the case that products are one to many with Category and subcategories are one to many with category.

Think about it. If a there can be many Categorys for a single Product, but only one subcategory for a Product, then there can be many Categories for a subCategory, which is the opposite of what you have as cardinality for aategory and subcategories: one to many

Normally, the relationship for Products Categories and SubCategories are as follows:

Category to SubCategory one to Many (Many subcategries per Category - Only one Category per subcategory)

SubCategory to Product: One to Many, Many products can be in each subcategory. but every product is in at most one subcategory.

Are you sure that isn't also your structure? ...

Charles Bretana 2009-12-17 03:46:30

May I ask for a suggestion on what the relational mapping would be if a product may have many categories but only one sub category. Also, the category may have many sub categories.

Ed 2009-12-17 03:57:33

Then the category arrived at through a product (where there can be many) is not the same category entity as would be arrived at from the products' subcategory - where there can be only one, cause each subcategory uniquely determines the Category. Thus is not a consistent set of relationships.

Charles Bretana 2009-12-17 04:06:23

It would be like saying entity A is one to Many to ENtity B which is one t omany to Entity C, which is one to Many to Entity A... This represents a logically inconsistent set of relationships.

Charles Bretana 2009-12-17 04:07:40

As @Willi suggests in another answer, isn't Product to SubCategory Many to one? Can't there be many products in the same subcategory ??? and isn;t it true that a product can't be in more than one subcategory ?

Charles Bretana 2009-12-17 04:10:38

Alright, I get it now. Thanks.

Ed 2009-12-17 05:20:00

+1 A:

Product to Subcategory: One-to-one

Shouldn't that be Product to Subcategory: Many-to-one

And why is not possible to make the subcategory_id column default null?

Willi 2009-12-17 03:49:42

ansaurus

tags:

views:

answers:

Null Foreign Key

related questions