0

Let's assume we have following database:

enter image description here

Or in pseudo sql format (all columns are not null):

create table road ( id int PK ) create table item_on_road ( id int PK road_id int FK road.id position_order int UNIQUE (road_id, position_order) ) create table rock ( id int PK FK item_on_road.id size float ) // and so on

We have a road on which we can travel in one direction.

Along the road we can find different things item_on_road in order we encounter them position_order like 1,2,3

Things that we find on the road can be different, from sign to gas_station.

Now I would like to ask a questions:

  1. Does this database design corresponds to db 1-5th normal forms? I would assume not, because we can create item_on_road entry without corresponding rock

  2. If it doesn't corresponds 5th normal form, how to do so?

Optional questions:

  1. If answer to 1 question is yes, how to ensure existence of rock if item_on_road created?

  2. If answer to 1 question is yes, how to ensure we can't create rock and sign pointing to the same id?

Improve this question
1
  • Please ask 1 specific researched non-duplicate question. PS NF definitions & algorithms are faqs. How are you 1st stuck? How to Ask Help center Commented Nov 22, 2023 at 5:35

1 Answer 1

Reset to default
1

Fifth Normal Form is about defining the set of valid combinations, if you have a need to enforce that in the database design.

It's not about requiring that a row exist in a referencing table. I don't know of any Normal Form that does that.

For example, if you needed to make the database design enforce that rocks can only occur on roads 12 and 34, you would decompose into another table to represent valid combinations.

Your current design allows rocks to appear on any road. If that's your intention, then it complies with Fifth Normal Form.

Question 3 & 4: how to ensure that there exists a child row, and that an item can only be one type of item?

You would need to reverse the reference:

Add a foreign key column for rock in item_on_road, which references the rock table:

alter table item_on_road add column rock_id int, add foreign key (rock_id) references rock(id);

This foreign key column is nullable. If the item is a rock, then fill in the value. If the item is not a rock, then leave it null.

Add more columns for the other types of items.

Then add a CHECK constraint to make sure that exactly one of these foreign key columns is non-null.

alter table item_on_road add check ( case when rock_id is null then 1 else 0 end + case when sign_id is null then 1 else 0 end ... ) = 1;
Improve this answer
2
  • It looks doesn't right to me to have multiple nullable ref columns in item_on_road table from database design perspective, seems like redundant data, but it solves my questions and simplifies a fetch as I can just join tables, thanks. I will accept your answer if there will be no better options. Commented Nov 16, 2023 at 21:05
  • 1
    Understandable. Frankly, most software developers just enforce this type of rule in application code. It might be handy to have the database enforce it so you are sure bugs in your code can never create anomalies in the data, but doing so comes with some inconvenience. Commented Nov 16, 2023 at 21:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.