sqljoinrelational-databasejunction-table

Should I store additional data in SQL join/junction table?


Are there any drawbacks to storing addition data in my join table / junction table.

For example, I am working on a database of trucking companies and I have 3 tables:

Table 1 - company,
Table 2 - trailer_type,
Table 3 - junction_table,

Each company can have more than one trailer type, but I also need a trailer count of each trailer type per company. The most logical place to put the trailer count would seem to be in the junction table with the company.id and trailer_type.id.

Are there any drawbacks to doing it this way, and, if so, is there a better way?


Solution

  • From the way you phrased the question, I think your intuition is mostly correct. You identified the junction table as the place to keep your counts. But you're hesitating, apparently because it's a "junction table".

    All tables are created equal. From the point of view of SQL, there are no fact tables, no dimension tables, no junction tables. There are only tables.

    A normalized design denotes a minimal key to identify each row. In your case, the natural key of the junction table is something like {company_id, trailer_type_id}. Is there information that's functionally dependent on that key? Why, yes, there is: ntrailers. Thus a column is born.

    So don't worry about what kind of table it is. Think about what the row means, and how it's identified. That will keep you on the fairway.