4

Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called

CHIP2DALE (or Dale2Chip) containing

id NUMBER UNIQUE NOT NULL
chip_id NUMBER NOT NULL
dale_id NUMBER NOT NULL

and an UNIQUE INDEX CHIP2DALE_UQ ON (chip_id, dale_id), because multiple relations don't make sense.

My question is: Do I still need the ID-column here? Is there any good reason for having this column? My rows are ultimatly identified by the combination of chip_id and chap_id GIVEN THAT I have absolutly no other columns here but the two.

3

You never ever need the id column in any table. It is a surrogate key which replaces the natural key for pragmatic reasons. Often this is because a narrow, monotonic key gives better performance, or because join clauses become cumbersome with multiple natural key columns.

Some shops have an id on every table as a standard. Consistentcy is good, so that would suggest having the id in this table, too. If this table is the parent of a 1:m relationship it can help performance to have the parent keys directly in the child table without having to follow surrogate keys.

It does take room on disk. If you have a gazillion rows that may be an important performance consideration. This would suggest omitting the id.

Broadly, in my experience it is best to omit the id from intersection tables, and add it later if the model changes.

  • One small remark to a surrogate key which replaces the natural key. Some natural keys are not unique. For example for persons John Smith is not unique and what else can you use but an ID? This can be anything from a national-id number or a sequence. I would say that you should never introduce an ID when you have a column in your table that is unique and will not change. – Marco Feb 19 '16 at 14:53
  • 2
    A good observation @marco. I would argue that if it is not unique it is not, by definition, a key. A person's name is the classic example where the numer of attributes to guarantee uniqueness is so large that a surrogate key is the only practcal solution. In my work we are forbidden by law from using some government-issued numbers as identities in databases, so care is needed. – Michael Green Feb 19 '16 at 15:07
  • @Michael_Green So we agree. BTW You are right about government-issued numbers but this counts also for other information that you collect on anything. First check the legality. – Marco Feb 19 '16 at 15:10
-1

Personally, I would have the id column in that table, and set it as an auto-incrementing integer, as that gives the ability to extend the content of that table by joining to another table that contains information about the relationship, such as when updates to relationships are performed, who performed them, etc.
Keeping the join table as small as possible improves the performance of joins if the data gets very large, and the additional table of relationship information can be joined in on an as-needed basis.
I've found this approach to be useful in the past of large (20+Tb) datasets.

Dave

  • 3
    You don't need another table to extend the join table with additional information. Just add new columns to the join table. Plus I don't see how adding a (unnecessary) column would qualify as "Keeping the join table as small as possible" – a_horse_with_no_name Feb 19 '16 at 13:24
  • What I was meaning was that you could add a large number of fields of variable length to the additional table, and only add data to the second table if necessary. This second table could then be LEFT JOIN'd as needed. The addition of the id field is a single field, which keeps the table as fixed format, which is quicker to process than variable row lengths when necessary. – Dave Rix Feb 19 '16 at 13:41
  • 1
    @DaveRix you can join such additional table using the composite primary key of (chip_id, dale_id), no need for autoincrement. – jkavalik Feb 19 '16 at 14:16
  • Yes, @jkavalik, I realise that, but for me I prefer consistency within the table designs, and simplicity of joins. You'd need to add both the join fields to the second table, and then join on both those fields each time the table was required, which I feel is a larger overhead than the single field addition. Also, single field joins are more performant than multiple field joins. – Dave Rix Feb 19 '16 at 14:18
  • 1
    @DaveRix The fields are primary keys in both tables, there is nothing faster and the difference is few machine instructions, thats really no performance difference. An autoincrement key on the other side has its own locking mechanism.. If you want to use the table efficiently then you usually need to index both (chip_id, dale_id) and (dale_id, chip_id) - making one of them primary and the other secondary key is very efficient for memory and speed (even more so when the primary key defines the clustering of such table). – jkavalik Feb 19 '16 at 14:23

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.