Null Foreign Key

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 wh开发者_如何学Pythonich 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?

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.

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?

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