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.

FK Naming

Hello,

I have 2 tables: Articles and Users.
These 2 tables are related by AuthorId (FK) in Articles and UserId (PK) in Users.

My question is: should the use the same name for the 2 keys, i.e., UserId?

Or it is normal to use AuthorId in Articles table and UserId in Users table. This makes more sense.

Just a naming question.

Thanks,
Miguel

It's normal to use AuthorID. Actually this would make it more clear (for anyone else looking at your database) that users from the Users table are the authors of these articles.

So, if you ever created for instance a ReaderComment table, you can have a ReaderID FK that is also a reference to the Users table.

Good Luck :)

|||

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

|||

Answer is May be or may not be

As per the general norms in paent and child tables if columns names are same there will be no confusion.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Ania:

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

It's all about what flavor you prefer. Personally, I try to avoid involving prefixing/suffixing columns for PKs and FKs. I mean, if you were ever curious if it is an FK or not, have a look in the table definition. Making complex column names like this will only make your SQL code harder to read and debug.

My suggestion: Keep names simple and intuitive. Don't use names for expressing relations. The DDL is perfectly capable of doing this already.

(Besides, you have no guarantee that a column named FK is actually an FK...)

|||

I typically name the foreign key like this:

<child_table_name>_<parent_table_name>_fk.

If there are more than one, I name it like this:

<child_table_name>_<parent_table_name>_author_fk.

<child_table_name>_<parent_table_name>_reader_fk.

As for the foreign key column names themselves, I typically use this method:

UserIdAuthor

UserIdReader

It's a bit more typing than AuthorId and ReaderId, but, at a glance, you know both it's fk lineage and its business purpose.

PS. I don't put the fk column name in the fk name because there may be more than one column in the fk!

First 5 Related Records?

I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do this
?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926Try:
select
o1.F_relNum
from
Orders o1
where
o1.RE_rel_num in
(
select top 5
o2.RE_rel_num
from
Orders o2
where
o2.F_relNum = o1.F_relNum
order by
o2.RE_rel_num desc
)
order by
o1.F_relNum
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do
this?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926|||That worked great! Thank You
"Tom Moreau" wrote:

> Try:
> select
> o1.F_relNum
> from
> Orders o1
> where
> o1.RE_rel_num in
> (
> select top 5
> o2.RE_rel_num
> from
> Orders o2
> where
> o2.F_relNum = o1.F_relNum
> order by
> o2.RE_rel_num desc
> )
> order by
> o1.F_relNum
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Wendy" <Wendy@.discussions.microsoft.com> wrote in message
> news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
> I am trying to determine what the first 5 related records to another recor
d
> are. I have created a table of matches where I have returned the unique ke
y
> value for each record in a one to many relationship. Preferrably, I would
> like to update the table that contains the F_rel_num with the 5 (or less)
> values for RE_rel_num in fields such as RE_rel_num1,
> RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
> RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would n
ot
> work as I need the top 5 for each F_rel_num. Does anyone know how to do
> this?
> I could easily write this in ASP or VB, but I need to be able to run this
as
> a regular SQL job, so I imagine I need to do it completely with T-SQL.
> F_relNum RE_rel_num
> 3 1633955
> 3 1353526
> 3 1137500
> 3 905264
> 3 732204
> 3 639101
> 3 488182
> 3 377705
> 3 365446
> 3 365445
> 3 313125
> 3 256899
> 3 254183
> 3 133409
> 6 214174
> 6 139273
> 6 117524
> 6 117520
> 7 1053160
> 11 1126433
> 11 857312
> 11 464240
> 13 555629
> 13 316781
> 13 302905
> 13 231447
> 14 644116
> 14 164926
>