-1

In general I know repeating information in tables are not considered a good database design

But I have a question for following particular design for multi level parent child relationship

Table_1_Table_2_Mapping
  - Id
  - Table_1_Id
  - Table_2_Id

Table_2_Table_3_Mapping
  - Id
  - Table_2_Id
  - Table_3_Id

I am just showing the two levels relationship here for example, but I have 5 levels of such parent child relation

So with the current design (which I feel is the correct design following the database normal forms) if I have to traverse to Table 1 from Table 3 I have to go via Table 2

So this way

Table_3 joins Table_2 join Table_1

This looks okay because they are just three tables, but what if I have to reach from Table_9 to Table_1, there will be more than 15 joins going from each mapping table to each main table.

It it still worth to follow the normal forms in that case also if I have say 9 child parent tables?

Or should I keep adding the repeating information in child mapping tables like this to keep the performance in mind (however this is not a good design as far as I know)

Table_2_Table_3_Mapping
  - Id
  - Table_2_Id
  - Table_3_Id
  - Table_1_Id //added this id to go directly to Table_1 from Table_3 skipping Table_2 join
1
  • You mention DB normalization but don't give any of the information by which to normalize, denormalize or join tables into one that is nevertheless normalized. "repeating" means nothing in particular. Where are you 1st stuck, following what published information modelling & DB design method, from what reference? PS Terms "parent" & "child" are too generic to be helpful. DB tables are not in hierarchies. There may be a tree or hierarchy of FK constraints, but those arise after base tables are chosen. PS Splitting a table into subtype tables is not normalization & isn't done for redundancy.
    – philipxy
    Feb 9 at 1:25

1 Answer 1

0

It it still worth to follow the normal forms in that case also if I have say 9 child parent tables?

Unless you are actually encountering the performance problems, I would recommend keeping things normalized for now.

You might, however, consider adding (a materialized?) view to make querying easier (mainly for you, but materialized/indexed views can make querying easier for your database engine as well).

If you are concerned about possible performance problems in future, do some experiments - load heaps of dummy data into your dev database and try some typical queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.