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

Question

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

Does RecordsTable need to exist at all. Wouldn't a view performing a UNION ALL between the individual record tables (and any common columns) serve the purpose better? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 10, 2012 at 17:59
+1 @Damien_The_Unbeliever; with a unique id across all records tables it'll be quick and it's a lot simpler to query. — Ben
– Ben, Commented Mar 10, 2012 at 18:05
@Ben - assuming you add a column, e.g. TableName, then the combination of TableName and Id should be unique with no further effort. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 10, 2012 at 18:08
@Damien_The_Unbeliever, I hadn't considered that. It assumes you know the table the record should come from at the time you query from it, but it's a lot, lot, simpler to implement... — Ben
– Ben, Commented Mar 10, 2012 at 18:10
so, I really have to union all. Does it have an impact to the runtime because as I have said, it will consist 30 or more columns and thousands of records each tables, and I after that it will be sorted by filing date? edit: I didn't saw the phrase "common columns". Ok, so yeah, it's really easy then — Jinnean
– Jinnean, Commented Mar 10, 2012 at 18:11

Andomar · Accepted Answer · 2012-03-10 18:21:44Z

1

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

answered Mar 10, 2012 at 18:21

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andomar Over a year ago

In Postgres you can use a sequence to create a unique ID. That allows you to make the RecordTable entry optional, though I wouldn't recommend it.

alexis · Accepted Answer · 2012-03-10 19:33:05Z

0

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.

edited Mar 10, 2012 at 19:33

answered Mar 10, 2012 at 17:56

alexis

50.4k18 gold badges108 silver badges173 bronze badges

4 Comments

supertopi Over a year ago

@Jinnean This will probably work, but imho it's not a very delicate solution. It will be a pain to maintain your RecordsTable. With some redesigning you can throw RecordsTable out of your database.

Jinnean Over a year ago

@alexis yeah, I know that user should not worry about that, that's why I abandoned that idea and looking for another. Though your answer is one of the answers I thought of but a foreign key mus not be null, right? and if that's really what I need to do, then I have to join those three tables

Jinnean Over a year ago

@supertopi I also thought of throwing out recordstable and just get all the ids and arrange it by filing date and then show it to the user. but that would mean I have to join every tables every now and then and sort it by filing date.

alexis Over a year ago

I suppose it depends on your DBMS, but foreign keys may be null. Primary keys cannot (actually I think in some DBMSs they can, but it makes no sense)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related