1

I am currently working on a Database-Design for an insurer. Example-Entities are "Customer", "Address", "Vehicle", "Building" etc.. So there are many Relations and every Relation has some Context. e.g. Customer-Vehicle: Customer can be owner, driver, lessee of a vehicle... Basically I see two options on how to handle that:

(1): A Junction Table for every relation, with the two foreign keys and a context-ID. This would lead to many junction tables, with partially very few data in it. Selects would be a little bit difficult, because of the amount of tables.

(2): One Junction Table for the whole Database. This table would contain two foreign keys, one key as to what relation it is (e.g. customer-vehicle) and a key for the context. This would lead to a huge junction table, but also simpler selects.

Which one is the better solution? Are there any main advantages/disadvantages or even other solutions I didn't think of?

Thank you for your help. If I didn't express myself clear enough or something is not understandable, please ask. (English is not my native language)

  • 3
    I don't see how the foreign keys will work in the second case (one junction table). Will you have foreign keys from that Junction table to all other tables? – ypercubeᵀᴹ Nov 24 '15 at 14:17
  • If you're doing a data model for a policy admin system, might I enquire as to what sort of risks it's supposed to cover? There are many roles a party can take with reference to a risk and/or claim. – ConcernedOfTunbridgeWells Nov 24 '15 at 14:58
  • @ypercube Sorry if i said that wrong. In the second case, i just want a junction table like 'keyofobject1' 'keyofobject2' 'typeofrelation' and 'context'. – kappadoky Nov 25 '15 at 17:55
7

The first solution is the best.

  • The second solution will give you less tables but in case that you need to join 3 tables with 2 junctions you need to use the big junction table twice in your SQL.
  • In case that you want to create constraints between a table and it's junction (is practically mandatory) then this is not possible in the second solution. Your data integrity will not be checked by the database.
  • The ER diagram for the second solution will look complicated for somebody that needs to get data out of your database.
  • 3
    It's possible to have referential integrity with a single table: you'd just need to have a separate column for each table and, of course, probably all of them would have to be nullable. That would still be madness, though, so yes, the first option is better regardless. – Andriy M Nov 24 '15 at 14:22
  • That would mean that you add a fixed value in your constraint definition. This is neither possible nor logic. – Marco Nov 24 '15 at 14:37
  • @Marco it is not clear what is not possible. Could you clarify? – dezso Nov 24 '15 at 14:59
  • When you create a foreign key constraint then you define which column(s) in the 'one' table should have the same value as column(s) in the 'junction' table. If the 'junction' table has a column that holds a column for the 'one' table then this is only true if the 'relation' field has the value of the 'relation' This is however not present in the 'one' table'. The foreign key constraint cannot be created. – Marco Nov 24 '15 at 15:05
  • 1
    @ Andriy Your example is the correct way. What I was meaning that when you have 1 table to hold all M-to-N ('junction') relations in your database then it is not possible to create foreign key constraints. This was what kappadoky wanted to use. – Marco Nov 24 '15 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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