FK. Can I do this?

Hello,

I have 3 tables:
[A] > Aid (PK)
[B] > BId (PK)
[C] > CId (PK), TargetId (FK)

TargetId should be related to both Aid and Bid.
Records created in C can be related to records in A or in B and TargetId can be either a Aid or Bid.

Can and/or should I do this?

Thanks,
Miguel

Hey,

You can relate TargetID to both A and B, but that means that that value has to reside in both tables, or you will get a constraint exception. That means that TargetID cannot be "either a Aid or Bid", but has to be both, if you set it up as a FK.

If that is the case, then sure, it's better to add constraints that are valid than to not have them for integrity sake.

|||

Although you cannot have an FK that say either A or B, you could build a trigger that checks the existance of id in either A or B prior to inserting the value in the referring table.

Or you could perhaps build a CHECK constraint. Dunno if it is possible to do a SELECT (from A and B) in a CHECK constraint though.

|||

Hi,

This seems really strange. I will try to explain it by using the real project I am working on:

I have 3 tables: Posts, Events and Files.
Each post, event and file can be a associated to one or many tags.

My idea was to create only one Tags table.
Note that each tag can have various associations.
It can be associate to various posts, events and files simultaneous.

My idea was to create a Tags table as follows:
[Tags] > TagId (PK), PostId (FK), EventId (FK), FileId (FK).

- Will I have problems with my Transact SQL queries?
- Will I have problems with .NET 3.5 LINQ?

The other 2 options I see are:

1. Having only one FK in table Tags, i.e. TargetId, which could be
associated with PostId, EventId or FileId ...
This does seem right to me. I feel I will have problems later on.

2. Have 3 Tags tables: for posts, for Events and for Files.
I would like to avoid having 3 tables but ...

I need to extend my decision to categories, ratings, etc.
So having 3 Tags tables, 3 Categories tables, 3 Ratings tables does not seem a good idea.

Could, someone, please advice me on this?

Thanks,
Miguel

|||

Hey,

I would have one tags table, but not have any foreign key. Because the tag could be in any one of those three tables, but not all of them, I wouldn't do that personally. That requires some extra care when you are dealing with the data, in ensuring that if you remove anything, you ensure that the tag isn't being used anywhere else.

|||

I am going for this:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

I think it is the best option. I hope. :-)

Thanks,

Miguel

|||

Yeah, actually that would be better That does make some more sense than not having a FK.