Denormalized SQL Table, Is this bad form...

[zachattack05]

If I have 2 or more tables and I need to have another table that is related to the data in those tables...how would I achieve that?

As an example:

Table A and B have totally different data and aren't directly related at all. I would like Table C to relate to a record in either Table A OR Table B but not both. Like, if there is an entry in Table C it could reference the PK in Table A or Table B...but since those are different tables, the PK can duplicate and I'm stuck figuring out (which key is which?) would it be better to have columns in Table C called something like "TableAKey" and "TableBKey" and have a relationship between those columns and the respective tables? If the relationship belongs to one table and not the other, one of them is null and the other isn't?

Any ideas?

[Mitja Bonca]

If column is marked as Primary Key, then is not chance of having two same values at all.
But what you can do, is to change the type of the primary key column to varchar, and add some character to the actual id, like A or B.

Exmple:
if id is a "number" like 1,2,3 and so on, save is as varchar, but with additional char like A1 or B1 (but as varchatr I repaet - so "A1", "B1", "B2,).
This way you will know from which table it is.

I dont really know why would you have it like this. Personally I would not do something like it at all. I would create two seperate tables - each per TableA and TableB (as auxiliary tables).

[Momerath]

You need to explain more why you need a table that references both tables A and B, but doesn't link records in A and B.

[zachattack05]

Let me attempt to clarify:

Imagine a database that holds medical records such as patient information, test results, names and tests offered at various laboratories, names and services offered by various physicians etc...

The user of the program maintains these lists themselves and also performs some (but not all) of the services in house.

Now:

Table A - A complete list of laboratory services offered by all laboratories, this includes test codes, costs and relationships to a laboratory table to identify the laboratory itself.

Table B - A complete list of physician services offered by all physicians, this includes billing and services codes, costs, and relationships to a physician table to identify the physician office itself.

Table C - A list of services available only at the software user's location. In order to populate this table, the user will need to enter what services they offer at their location.

Some services from Table A are independent of a service from Table B and some are not. For example: a laboratory may perform a test, and depending on the outcome of that test will either report the result directly to the patient or to a Physician for review/consideration. The review of a result from a lab is a service that a physician can offer. But, they are independent of each other.

When completing a row in Table C, the user would need to indicate (in some fashion) a relationship that is like this:

#1 A laboratory test performed at "Laboratory A" and reported directly to the patient.
#2 A laboratory test performed at "Laboratory B" and reported directly to the patient.
#3 A laboratory test performed at "Laboratory A" and reported directly to Physician A.
#4 A laboratory test performed at "Laboratory B" and reported directly to Physician A.
#5 A laboratory test performed at "Laboratory A" and reported directly to Physician B.
#6 A laboratory test performed at "Laboratory B" and reported directly to Physician B.

where Table A has:

#1 A laboratory test performed at "Laboratory A"
#2 A laboratory test performed at "Laboratory B"

and Table B has:

#1 A review of a laboratory result performed by "Physician A"
#2 A review of a laboratory result performed by "Physician B"

These possible combinations need to be entered into Table C in some fashion along with additional data such as unique patient billing codes, medical codes and other related data.

If the table is set up with multiple columns like "TableAKey" and "TableBKey" and those relationships are made, a value combination of: 1,1 in Table C, would indicate the row is referencing A laboratory test performed at "Laboratory A" and reported directly to Physician A. whereas a value combination of 2,null would indicate the row is referencing A laboratory test performed at "Laboratory B" only.

The only issue with this approach that I can see is if there is a new entity (like Laboratory, Physician) that would require a new table, I would have to alter Table C to add a new column and build a new relationship.

Table C, BTW would be used in a reference to a "Patient Encounter" table. When a patient visits somewhere to have services performed, a lab test may be ordered, so a line in the "Patient Encounter" table would refer to something in Table C so that a history is kept on patient interactions and can be traced back all the way to the laboratory and physicians involved in that patient's care.

It's confusing and it's hard to explain. Did that help at all?

[zachattack05]

Here's a drawing that might explain what I'm after a bit better:

[lolafuertes]

IMO The best way is to add a field indicating the source table, then another with the key value to the source service.

The PK would be both fields. This way, you can have the same key value, but always coming from a distinct table.

Be aware to add a DELETE trigger on each source table in order to delete the related record in the C table.

Hope this helps

[zachattack05]

Hey that's actually not a bad idea!
Question though: The field indicating the source table, would that only be present in Table C, or also in the source tables?

I thought about doing that as well, but dumped the idea because I thought it was a waste of space and a source of confusion to have data that really doesn't mean anything.

[lolafuertes]

I will put the source table field only on C.

The referential integrity between table C and the sources A and B is to be mantained using triggers.

Hope this helps.