CODEWITHSUNDEEP
sqlpostgresqlforeign-keysmultiple-tables
Jinnean
Jinnean

Reputation: 97

SQL - Foreign key referencing different tables' primary key (Postgres)

This is for a project. I am making up a database for different kinds of records for an office. There is a table collecting all the primary keys of those record tables. So, I need to have a foreign key referencing those tables. And I am lost. I asked my very loyal friend google but I didn't understand some of the possible answers for this.

Here's what I have come up (the names of the tables are not their real names, just want it to be generic here).

Edit: I am using Postgres

RecordsTable:

recordId -> references record1 id,record2 id,record2 id
docId -> identifies what kind of record it is
filingDate

Record1:

id
attribute2
attribute3

Record2:

id
attribute2
attribute3

Record3:

attribute2
attribute3

Actually,Record1, Record2, and Record3 have more than 30 columns each (imagine a birth certificate e.g.)

RecordsTable will be the one shown to the users. record1-3 will only be shown if the user needs to edit something.

I thought, I would make record 1-3 reference recordstable but it will be troublesome to the user as he/she will need to input first in the recordstable and that's not what recordstable is for. It's just to show the summary of all the records filed in the office since it will be not good to show all of those at once.

If there's anything unclear, please let me know so that I can explain it more. Thanks in advance

Upvotes: 1

Views: 2931

Answers (2)

alexis
alexis

Reputation: 50220

The user should not have to worry about what goes in which table, that's the job of your application.

If you need your main table to reference any one of three different tables (and they have such different columns that you can't unify them into one table), set up your main table like this:

RecordsTable:
Id -> primary key for the table
docType -> identifies what kind of record it is: 1, 2, or 3
docID1 -> Foreign key to table1, if docType = 1; NULL otherwise
docID2 -> Foreign key to table2, if docType = 2; NULL otherwise
docID3 -> you get the idea
filingDate

But if you have three kinds of entities with very different contents, do you really need to list them all together? Maybe you don't really, in which case you can just maintain them as separate tables. If it does make sense to list them all together, you have conceptually one entity ("document", by the sound of it) with three subtypes. It might make sense to make it easy for yourself and store them in one table, with lots of empty columns (VARCHAR columns don't take up space when they're empty). It's a little bit wasteful, but today's computers have space and power to spare, and your time is valuable. But, you object,

It's just to show the summary of all the records filed in the office since it will be not good to show all of those at once.

That's a non-issue! Do a SELECT query with just the columns you want to show for the summary. If the user chooses a record, then do a SELECT * and show the full result.

If you do want to go to the trouble of splitting out the non-common fields, do it as above.

Basically, you need to do a little bit of programming. Don't think of your database design as your entire program: The job of the database is to store data, the job of the application is to do things with them.

Upvotes: 0

Andomar
Andomar

Reputation: 238296

Well, I'd remove the docId column. You can tell the type of a record from its presence in the other tables. It's good practice not to repeat yourself.

To keep id unique across the three tables, you'll need a single identity column. (Unless you're using Oracle or Postgres, if so, edit your question.) So users will have to obtain a record id from the Records table before they can fill out the details. Perhaps you can create a user interface that does this for them.

RecordsTable: id int identity, filingDate
Record1: id int foreign key references RecordsTable(id), ...
Record2: id int foreign key references RecordsTable(id), ...

You'd retrieve the document type like:

select  case
        when record1.id is not null then 'Type1'
        when record2.id is not null then 'Type2'
        ...
        else 'Unknown'
        end
from    RecordsTable rt
left join
        Record1 r1
on      rt.id = r1.id
left join
        Record2 r2
on      rt.id = r2.id

Upvotes: 1

Related Questions