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);
- 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.