4

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?

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
zcleft
  • 103
  • 2
  • 8
  • Don't store this data on the database. Use a query to calculate the counts per company. – Siyual Jul 15 '16 at 18:53
  • At this point, the query would only return how many "different" trailer types the company has as I am trying to determine the best method to store the data containing "how many of each" trailer type the company has. – zcleft Jul 15 '16 at 19:24

3 Answers3

12

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.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
2

First, it is fine to store additional information in the junction table. For instance, the date created is often very interesting. And, there are many examples of entities that you can think of as junction tables -- say a billing record that connects a customer to an invoice -- and the records naturally have additional fields.

However, the count is not something that you would store under ordinary circumstances. This type of information should go in the Company table, particularly if it is fixed over time.

If it is not fixed, two options come to mind. The first is a slowly changing dimension where you store the number of trailers for a company along with effective and end dates for the value. A viable alternative is to store the value in the junction table. It is not the first choice, but it might be a good solution under many circumstances.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If he needs the counts by trailer type, and does not have records elsewhere for each trailer (as assumed by Siyual's comment), it wouldn't really make much sense to store it directly in `company`. – Uueerdo Jul 15 '16 at 19:15
  • Thanks, @Gordon that answers my question. In my scenario, the company may have 25 type A trailers, 30 type B trailers etc., so it would be a lot of extra columns in the Company table as we are talking 10+ trailer types and to create an additional table to store the count would be a lot of redundancy. – zcleft Jul 15 '16 at 19:17
0

You may wish to think of your schema a little differently than as a many-to-many relationship between companies and trailers: that's the reason you have a junction table.

How about thinking of it this way:

  1. Companies have a one-to-many relationship with Trailers
  2. Trailers have a many-to-one relationship with TrailerDescriptions.

So, your Trailer table will have these columns:

 company_id
 trailer_description_id
 count
 etc.

You might even choose to enumerate the individual trailers, by adding columns and setting "count" to 1. 

 trailer_id
 company_id
 trailer_description_id
 count = 1
 date_placed_in_service
 license_plate_number

etc.

O. Jones
  • 103,626
  • 17
  • 118
  • 172