0

I have three entities: Document, Asset, and Client

A Document can be linked to several assets. An Asset can have many documents.

A Document can be linked to several clients. A Client can have many documents.

Is it better to have two join tables...

asset_document
    asset_id
    document_id
    ...more fields

client_document
    client_id
    document_id
    ...more fields (same fields as in asset_document)

Or just one join table?

document_relationships
    document_id
    object_id (could be a client_id or asset_id)
    type (whether it's a client or asset)
    ...more fields

My guess is that the first one is better but I can't explain why. I'm looking for a strong argument against the second one. My co-developer prefers the second one to reduce duplication and lesser tables to maintain and lesser tables to join in queries.

Take note their could be more entities where Document will be linked to but ...more fields will stay the same.

Improve this question
1
  • @Vérace There no nullable fields. As written above, the ...more fields parts are exactly the same fields in all tables (e.g. created_at, modified_at, etc).
    – Noel Llevares
    Commented Oct 4, 2017 at 5:42

1 Answer 1

Reset to default
0

You should definitely two separate tables!

  • What if, in the future, documents get linked to something else?

As it is anyway with your documents_relationship table, you are most likely going to have fields that apply to clients and not assets and vice versa!

This will mean NULLs. Good frule of thumb IMHO, a field should never be NULL by design for its entire life. Note: this does not mean that NULLs should never be used!

Improve this answer
2
  • There no nullable fields. As written above, the ...more fields parts are exactly the same fields in all tables (e.g. created_at, modified_at, etc).
    – Noel Llevares
    Commented Oct 4, 2017 at 5:43
  • Yes, but what if you link documents to something else in the future? You can use views to get aggregates. I just feel that the one-entity, one table desingn is "cleaner"! There will always be an element of opinion in database design. I just get very (!) wary of tables that try to be two things at once. Before you know it, it's three, then four... and pretty soon, your system is an unwieldy mess
    – Vérace
    Commented Oct 4, 2017 at 5:48

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.