1

I have this two tables:

  • Documents(ID, IDTypeOfDocument, BigIntReference);

I have a documents table that has and ID that is an autonumeric bigint. Also I have categories, and also it has a big int as reference that is unique for category, but for two categories, I can have two documents with the same reference, one for one category and the other document for the second category.

Until now, in all the tables that needs to referes to the Documents table, I am using the ID of documents as FK.

But now I have a new table, that I only want to related with one kind of document, so I am thinking that I have two options:

  • SecondTable(ID, IDDocument);

or

  • SecondTable(ID, IDDocumentCategory, BigIntReference);

The first option it seems the most obvious, but in this case, the user will add a record in the second table knowing the BigIntReference of the document, so I guess that to save the record in the second table, it is more easy the second way.

I think that because if I use the first option, I have to do that:

  • Get the document which IDCategory is 1 (always it is one) and the BigIntReference is the reference given by the user. This is a query to the database.

  • Create the record and set the ID of the document.

  • Save the record.

However, with the second option, it is more simply:

  • Create the record setting 1 as IDDocument and set the BigIntReference given by the user.

  • Save the record.

I avoid the needed to get from the database the ID of the document.

The second option it seems a good solution, but I don't know if it is very correct, to use as foreign key two fields of a table that are not the primary key. In this case, BigIntReference can't change and IDCategory / BigIntReference is a unique value.

But by the other hand, perhaps it is to complicated my model only to avoid one extra query to the database.

Thanks.

Improve this question
1

1 Answer 1

Reset to default
3

Given a fixed dataset a composite key can usually be found to uniquely identify records in a table.

Given a growing dataset any composite key can have it's uniqueness destroyed by adding one more record.

The problem with composite keys is they aren't yours. They're data. They belong to the world. And the world doesn't care about your databases integrity.

So if you're lucky enough to find composite keys that work today, great! Use them to set up primary and foreign keys that don't represent anything other than keys (known as surrogate keys). Then they're yours. They wont change. They wont conflict with new data as you learn more about the world. Remember, there is no database integrity rule strong enough to stand up to a court order. Make sure your keys are something the world doesn't even know about.

Improve this answer
8
  • 1
    I once was on a project where we removed a field from the composite key used across literally a hundred or more tables and had to update all the queries and joins etc. Then not too long after we reversed course and went back to the original. Surrogate keys are your friend.
    – JimmyJames
    Commented Apr 21, 2023 at 20:09
  • 1
    @JimmyJames I'm sensing agreement but let me be certain. When you say surrogate, are you using this definition?
    – candied_orange
    Commented Apr 21, 2023 at 20:21
  • Yep, pretty much total agreement. If I was going to pick nits, I would be more forceful about preferring surrogate keys. For a long time now, I expect a really good reason for not using a surrogate key. That should be the default.
    – JimmyJames
    Commented Apr 21, 2023 at 20:29
  • @JimmyJames For a while my policy has been to only use composite keys (and apparently compound, I have a lot of words to define here) when structuring dead data. When anything goes live it's using surrogate keys (still no idea why they call it that. Always forgetting the name).
    – candied_orange
    Commented Apr 21, 2023 at 20:34
  • I wish there was better support for surrogate keys in standard RDBMS. There might be platforms that do what I want but I haven't figured it out yet. But I don't see why we can't get an automatic surrogate PK by simply defining the logical keys. But in any event, there are some scenarios where I have found it doesn't make sense to create a surrogate key but I'm struggling to recall exactly what they are at the moment.
    – JimmyJames
    Commented Apr 21, 2023 at 21:17

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.