2

I do not know which is the best approach in terms of database modeling. I have 3 kind of entities "Shipping Costs", "Billing costs", "Order costs" with different columns and different primary keys but they have in common that every entity could have one or more lines (detailed costs). I have modeled every entity with his respective "XXX_line" table (option 2) but lines had the same columns and are the same entity.

For that reason my colleague propose me the first option but I do not know how to model that in postgres (probably need an intermedite table?).

diagram

Thanks for your time.

4
  • 1
    Hi, and welcome to dba.se! Please read this about images. Please go to dbfiddle.uk and construct some tables (I recommend using PostgreSQL as a model) and the relationships between them - FOREIGN KEY / REFERENCES. This might help. Dec 2 '21 at 17:40
  • 1
    No need for an intermediary table, just set up 3 nullable foreign keys from your detail table to the 3 cost-type tables. db-fiddle.com/f/7Kri8abRcXB5tpBv3sZvwr/0
    – AdamKG
    Dec 2 '21 at 19:35
  • Very usefull web pages @Vérace-getVACCINATEDNOW. I will try to use dbfiddle.uk and edit the message. Thank you.
    – javher
    Dec 3 '21 at 6:08
  • @AdamKG thanks for your response. Is a good approach in terms of modeling to have 2 columns always in null? Because a line is only from shipping, billing or order.
    – javher
    Dec 3 '21 at 6:08

Your Answer

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

Browse other questions tagged or ask your own question.