CODEWITHSUNDEEP
sqlsql-server
gtestasker
gtestasker

Reputation: 81

Relationship between multiple tables and a common table

I have 3 tables

All tables take in parameters which are stored in the Parameter table. Once a parameter is used in one table it cannot be used by any other table.

The Id values of all tables need to be present in the Parameter table. I created EntityId and EntityType columns in Parameter table which stores a JobType or JobSubType or JobSubSubType Id and the type of the entity.

How do I define relationships which ensure the values in EntityId belongs to JobType, JobSubType or JobSubSubType?

Simply having foreign keys on EntityId doesn't help. I do not want to create separate columns for JobType JobSubType or JobSubsubType Ids, as 2 columns out of 3 are always going to be null.

Example Parameter Table

+-------------+----------+---------------+--+
| ParameterId | EntityId |  EntityType   |  |
+-------------+----------+---------------+--+
|           1 |        2 | JobType       |  |
|           2 |        6 | JobSubType    |  |
|           3 |       11 | JobSubSubType |  |
|           4 |        4 | JobType       |  |
|           5 |        6 | JobType       |  |
|           6 |       12 | JobSubSubType |  |
+-------------+----------+---------------+--+

I had created foreign keys on the JobType, JobSubType and JobSubSubType tables with EntityId. When I was inserting values for JobSubSubTypes in parameter table I got an error.

Upvotes: 0

Views: 1211

Answers (1)

Alan
Alan

Reputation: 1428

You could do it in two ways. First way would be to have a common base table of types that is used to serve the three tables with a common ancestor ID and thus each table would have IDs that are unique across all three tables, and you could then reference the ID in one column, with perhaps a second column to indicate which table was referenced.

I believe this second approach would be preferable: Have only one types table, but use a ParentID as a self pointer to allow for sub types, sub sub types, and indeed sub sub sub sub sub sub sub sub sub types. Then all you need do is reference the ID.

To keep the existing IDs (as asked in the follow-up comments, you add an additional field identifying the table - as mentioned in the first paragraph. The join would then play out like follows:

SELECT
    col1, col2, ..., coln
FROM
    mytable1
    INNER JOIN types on mytable1.typeID = types.ID AND mytable1.typetable='Type'
UNION ALL
SELECT
    col1, col2, ..., coln
FROM
    mytable1
    INNER JOIN types on mytable1.typeID = types.ID AND mytable1.subtypetable='SubType'
UNION ALL
SELECT
    col1, col2, ..., coln
FROM
    mytable1
    INNER JOIN types on mytable1.typeID = subsubtypes.ID AND mytable1.typetable='SubSubType'

Upvotes: 1

Related Questions