MySQL - Relationships and Foreign Keys in Normalised DB

[cksanders1]

Hi there!

 

I'm working on a web portal that links to a database - users are able to upload and search for data within the database. I am working with XAMPP and the Server version is 5.5.8

 

I am trying to create a normalised database with foreign keys by using InnoDB. I am currently adding data to the DB and the relations I have created I don't think are correct.

 

 

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles.

 

It would be great if some one could help me out!

 

There are two relationships in question:

 

QUESTION ONE.

 

tables involved are orders,  industry_sector, and industry_sub_sector. The industry_sector table has an optional 1:m relationship with the industry_sub_sector. orders table has a mandatory m:1 relationship with the industry_sector table. The industry_sector and the industry_sub_sector tables are complete as those are the only possible values to ever be included. The orders table so far is empty as I don't think I will be able to reference which IndustrySubSectorValue that particular OrderID will have.

 

Possible Solution 1: I know that I could add another field to the orders table - IndustrySubSectorID - however that would create many NULL values as each order doesnt have to have a IndustrySubSectorValue (the relationship is mandatory).

 

Possible Solution 2: I could make a bridge entity that would include the following fields: OrdersID, IndustySectorID, IndustrySubSectorID. However that would be creating a relationship with orders. Itsn't that incorrect as industry_sub_sector table should have a relationship industry_sector NOT orders?!

 

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles. It would be great if you could help me out.

 

QUESTION 2.

 

This question is similar to question 1 in that it is about relationship between 3 interlinked tables, namely: orders, distribution_channel, and distribution_channel_details.

 

The distribution_channel table has a 1:m mandatory relationship with orders table. The distribution_channel_details has an optional 1:m relationship with the distribution_channel table. (basically distribution channel details are the 'notes' on how that particular distribution channel was used for that specific order)

 

Possible solution would be to do the same as question 1 - but that would create null values as each order doesnt require a DistributionDetailValue.

 

 

If you need any more detail about either question please let me know, and sorry if I havent explained it all to clearly

 

Thanks so much!

 

[attachment deleted by admin]

[fenway]

Sorry, I don't understand the problem.

[cksanders1]

As I said I am unable to add rows to the orders table because foreign key constraint failed. The relationships I have in place are wrong. I am looking for help with the relationships between:

 

1. orders, industry_sector, and industry_sub_sector

2. orders, distribution_channel, and distribution_channel_details

 

 

I have attached the tables to this post showing all the possible values. However distribution_channel_details is empty as those value will be different for each order added to the database. orders table is empty as well as I am unable to add data because foreign key constraint failed.

 

I realise I could easily add both DistributionChannelDetailID and IndustrySubSectorID to the orders table - however that would creat a large amount of NULL values as both DistributionChannelDetailValue and IndustrySubSectorValue are optional for each order added.

 

So simply put : do I create a bridge entity that is directly linked to the orders table for the industry_sub_sector and the distribution_channel_details respectively ??