OTM relationship from many table

Other SQL Server Topics
1

Hi,
I want to create OTM relationship from several source tables to one target tables. the thing is that after i created it, the target table contained FK column for each source table, of course that this approach is not good as many fields are unnecessary.
Could you please advise how can i solve it using better approach.

Keep in mind that i use entity framework with code first.
Thanks.

2

Sounds like that's the problem (i.e. in a database design sense) ... can you give an example please?

3

Sure Kristen,
Many flows in my application should contain photo/s (one or many), and each flow has its own table.
So i wanted to save all the paths in one source table (BlobFile) and add FK column for the entire source tables. but SQL can't contain one column that keeps FKs from different tables.that's why it creates column for each table

so i'm trying to sort it out...
I hope the above explained better.

Thanks.

4

So you have something like:

TableName
KeyValue

where Table Name is the name/indicator of which table the record is linked to, and KeyValue is the Primary Key of the record (in that table)?

We do that sort of thing - indeed, we have some instances where the KeyValue is a multi-part key, and the number of columns varies from table to table :frowning: but we don't enforce them with a Foreign key constraint. We use a Trigger to ensure that the child record exists, and to not allow deletion of a Parent if there are still any Child record.

So you could have:

Parent Record Table

Links Table

Child Table1
Child Table2
...

and the Links Table would have the PKey of the Parent Record, an indicator of which Child Table that link related to, and the PKey of the Child record.

Its properly messy though ... :slight_smile:

5

Thanks for your reply Kristen.

I'm not sure i fully understand you as the first part refers to ignoring FK restriction and creating of trigger.
Both seem wrong to me (in case i understood you correctly...) as SQL DRI must remain and trigger seems too heavy for my scenario.
Second part refers to linking table (which seems as a good solution) but why i need several child.

Please allow me to explain my scenario better.

i have 5 tables (A, B, C, D and E with no correlation between them) that should save photos. each record in each table can save 1 or many photos. what i wanted to achieve is to have one table (BlobFile) that stores all the paths of the photos. so it seems reasonable that BlobFile will contain one column with the primary key of each source table (A, B, C, D and E).
A OneToMany BlobFile
B OneToMany BlobFile
C OneToMany BlobFile
Etc...
But, SQL can't have one FK column that stores primary key of different tables. so it creates FK column in BlobFile for each source table, A_FK, B_FK, C_FK, Etc...

What i had in mind is to create a linking table, as follows:
A OneToOne LinkingTable OneToMany BlobFile
B OneToOne LinkingTable OneToMany BlobFile

Is this seems as a good solution?
Please advise...

Thanks.

6

You can't have an FKey connecting to multiple tables, conditionally. So IF you want to do that you will have to use Triggers (or similar) to enforce it.

You could have multiple LINK tables, and then EACH Link table WOULD have an FKey.

We don't do it that way because of the effort to maintain all the (duplicated) code for each of the link tables, but I suppose you could have tables: LinkA, LinkB, ..., LinkE and then have a single VIEW that joined them together so you could use them as-one.

7

Hi Kristen,

I'm not talking about multiple LINK tables but only one.
Each source table will have a relationship (OTO) to the same LINK table, then each source table will hold the PK of the relevant record in LINK table.
Then LINK table will have a relationship (OTM) to BlobFile.

This scenario keeps the DB with only relevant data and not multiple tables to maintain.
What do you think?

8

I think I have explained the choices already.

You can have either:

One link table, multiple "child" tables -> you can not use FKeys, you will have to use Trigger or similar to enforce referential integrity.

or

Multiple link tables, each joining to only one Child table -> you can use FKeys but I think maintenance of this solution will have duplicate code and thus the risks of bugs e.g. if a change is made in one place but not in all the other, similar / duplicated, positions in the code.