0

I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed.

The new table looks something like this,

UUID(pk)   PartId(pk) Somecolumn1 SomeColumn2
asdf56das  0001       val1        val2
u44ighhuh  0002       val3        val4
u44ighhuh  0003       val5        val6

I want to associate UUID column with data from other tables.

One method is to add a XML column to this new table which would store the associations as,

<tablename><pkcolumn1>value</pkcolumn1> 
<pkcolumn2>value2</pkcolumn2>...........<pkcolumnN>valueN</pkcolumnN> 
</tablename>

With this approach there is some performance cost involved in creating and parsing XML.

One other way is to have a new "Mapping table" which will map the new table with the other existing table that I am trying to establish an association with.

Are there any other ways we can solve this?

Suresh
  • 69
  • 5
  • In a relational database *tables* represent relation(ship)s/associations. ("Relationship" unfortunately is also ubiquitously incorrectly used to mean FK.) Add a table with a uuid etc from that table & whatever you want to record such a subrow is associated with in whatever way, for each such relation(ship)/association. *Normalization* tells you whether you can/should combine tables. Time to read a book on information modeling, the relational model & database design. – philipxy Aug 06 '18 at 17:45
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Aug 06 '18 at 17:47

1 Answers1

0

A Foreign Key can have multiple columns. So there's no reason you can't just use for each other_table either

1) A Foreign Key on new_table to other_table.

Or,

2) A new linking table with Foreign Keys to both new_table and other_table

With a linking table you can make the relationship 1-to-many, many-to-1 or many-to-many by declaring appropriate unique constraints.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks for your answer. For Approach 1 - The schema of the "other table" cant be changed to accommodate a FK. Approach 2 - The "other table" can be many tables and not just one, with varying count of PK's(composite key). If we have a N "other tables" we would need N "Mapping tables" – Suresh Aug 06 '18 at 16:18
  • @Suresh that's when you use a linking table with a 1-to-1 relationship to the "other table". – David Browne - Microsoft Aug 06 '18 at 16:20