What is the best design for referencing one record in one of multiple tables?

[IliaPreston]

Let's say I have three tables like this:

   sql = ""
   sql = sql & "create table if not exists TBL_A ( "
   sql = sql & "TID   INTEGER Primary Key, "
   sql = sql & " ......, "
   
   Cnn.Execute sql

   sql = ""
   sql = sql & "create table if not exists TBL_B ( "
   sql = sql & "TID   INTEGER Primary Key, "
   sql = sql & " ......, "
   
   Cnn.Execute sql

   sql = ""
   sql = sql & "create table if not exists TBL_C ( "
   sql = sql & "TID   INTEGER Primary Key, "
   sql = sql & " ......, "
   
   Cnn.Execute sql

All three primary key columns are calculated and populated by my program (I do not leave them to be auto-populated by the Sqlite engine)

Now, I need to add a new column to all three of the above tables.
The new column is supposed to uniquely identify (in other words is supposed to uniquely reference) one of the records in only one of these three tables.

For example A.NewColumn can hold a value that is equal to A.TID in one record in A
Or it can hold a value that is equal to B.TID in one record in B
Or it can hold a value that is equal to C.TID in one record in C.

In other words the new column in a table can reference another record in the same table or in any of the other two tables.

I can design this whole thing in two different ways:

Design # 1:

I populate the primary key columns of these three tables in a way that each value inserted in any of these three pirmary key columns in unique across all three tables (and not just unique within the one table that it belongs to)

Then the new column (in all three of these tables) can simply be called REFERENCE_TID:

   sql = sql & "REFERENCE_TID      INTEGER    , "

This way, if in some other part of the project, the program needs to find the referenced record, it can pick the value of REFERENCE_TID, and try to find it in all three of these tables (three separate queries).

Design # 2:

I populate the primary key columns of these three tables in a way that each value inserted in any of these three pirmary key columns in unique only within that table, but that same value might also exist in one of the other tables or both other tables.

Then in order to reference, I need to have TWO new columns (instead of one column) in each of thease three tables:
Then the new column (in all three of these tables) can simply be called REFERENCE_TID:

   sql = sql & "REFERENCE_TID               INTEGER    , "
   sql = sql & "REFERENCE_TBLE_NAME      TEXT    , "

The column REFERENCE_TBLE_NAME identifies the table that it references. This column can hold one of these values: "A", "B", "C"
And the column REFERENCE_TID identifies the primary key value of the table, the name of which is stored in REFERENCE_TBLE_NAME

This way, if in some other part of the project, the program needs to find the referenced record, it can pick both of these two values (REFERENCE_TBLE_NAME and REFERENCE_TID) and try to find the record that it is looking for, in all three of these tables. Again, that means there will be three separate queries

Which one of the above two designs is better?
And why?
And, is ther any other design that is better than both of them?

Thanks.

[Schmidt]

I think, the main-problem with your current Schema is, that you have 3 separate tables (nearly similar in design),
in the first place...

If they are "roughly similar" in their Schema, please "unify them" into a single table first

Perhaps introducing additional "category"- or "type"-fields, to be able to distinguish between the slight record-differences.
Another option is, to put the Fields which make the 3 current tables different from each other, into a separate "Extra-Properties"-table -
(which then not always gets an extra-record-entry, in case the record in the main-table is sufficiently described with the generic fields already.

So, if you end up with only one large, unified "main-table", you ony need a single "self-referencing-ID-Field" in that table, to solve your current problem.

Olaf

[FunkyDexter]

the new column in a table can reference another record in the same table or in any of the other two tables

That's your problem. A foreign key can only reference one table. You have a few choices.

As Olaf says, you could combine the three tables into one. You would choose to do this if all the entities are kinda the same but with small differences. If you do this what you're really saying is that they're different types of the same entity - in other words, it's an inheritance structure. There are three broad ways of implementing inheritance in a relational database and you can read about them here. This will then allow you to have a single self referencing foreign key in the table.

If, on the other hand, you think that the three tables contain substantially different entities, you might not want to combine them. In that case you actually need three different foreign keys, one pointing to each table. It's not entirely clear from your description but I get the impression that only one of these should be populated for any given record (i.e. it can't point to both TableA and TableB). Assuming that's the case you can enforce it with a simple programmed constraint.

[Zvoni]

Isn't this scenario that Entity Anti-Pattern we had a discussion about some months ago?

[FunkyDexter]

Are you referring to the Entity Attribute Value Pattern? If so I'd say not quite, but it's headed in that direction. The EAV pattern has it's roots in inheritance but it's really inheritance taken to a ridiculous degree. It basically says "Everything inherits from an undefined object that has some fields so lets just model an undefined object with undefined fields". It's true, but it's not useful and is damaging in design terms. It's not a flexible schema, it's the absence of a schema.

I have to say, introducing category/type fields to records is always a warning sign to me because it's often the first step towards EAV. In this case I felt it was a valid suggestion if the various entities in the three tables really do represent an inheritance of similar entities. If that's the case it really just makes up part of a compound primary key which is perfectly valid in Third Normal Form. I personally prefer to avoid it but it's not EAV.

The crux of the issue is "do these tables genuinely represent different aspects of the same type of object".

[Zvoni]

Hmmm, what about "designing" it this way:
A single table with the common "primary" fields
A single Table with the "secondary" fields (kind of generic fields)
a "connecting" table in m:m-relation between the two? Or would a 1:m-relation be enough (no connecting table, just Foreign Key in secondary table)?

I admit, i'm not sure if i've formulated my idea the correct/complete way....

[FunkyDexter]

Let's simplify. Here are the imperatives:-
1. There are three different entity types: A, B and C
2. Each entity can reference a single entity which may be of any of the above three types
3. A foreign key can only ever reference a single entity of a pre defined entity type

Those three imperatives only off two possible solutions:-
1. Each entity must have three foreign keys, one to each entity type or
2. The three entity types must be combined into one that can be referenced with a single.


It might be easier to stop thinking about all three tables and just consider Table A. It need to reference Table A, B and C. Considering this, in combination with imperative 3 above, it's quickly becomes clear that either Table A requires three foreign keys or Tables A, B and C must be combined into a single table.

Whether solution 1 or 2 is the right choice depends on the context and the OP would have to decide. If the choice is to combine the tables then that is inheritance and there are a number of ways to achieve it as per the article I linked but that's secondary - the OP needs to decide whether the tables should be combined or kept separate first.