0

We have a table meant to store a property of something else:

Table: property
Column: id, PK
Column: owner_id, FK
Column: name
Column: value
Column: kind

we generate a number of properties based a list that's defined else where. So if there are five properties in the list, we generate five property records with the name matching each respective property in the list.

The issue is that the value some of these properties can draw from other lists.

For example we might have a property called favorite_fruit and this is linked to the fruit table or a property called favorite_car that's linked to the car table table that they can select from (think drop down).

Something like this:

Table: property
Column: data_id
Column: data_table_kind<-- this could be `fruit` or `car`

Is it possible to create a table where we can choose which table (stored in data_table_kind) the foreign key stored in data_id points to?

1
  • No, you cannot. You could use a foreign key in the other direction, which would make more sense. But I doubt that yours is a good data model. It smells of entity-attribute-value. If you need to have a variable data model, perhaps carefol use of JSON would be an option. Jun 10 at 1:36

1 Answer 1

0

You can't do that you'll have to have one column for each foreign key.

Null values don't use any storage so this is not going to cause the records to bloat but in extreme cases you may come up against the column limit of 1600 columns per table.

you could try a partitioned table, but that requires all tables to have the same columns.

OTOH if you don't need relational integrity just don't declare the foreign key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.