| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2007-10-16 : 17:08:27
|
| Hello,I need an advice: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).- Is this the way to do this?- 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.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 option 2 really seems bad idea.Could, someone, please advice me on this?Thanks,Miguel |
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 17:17:46
|
| "Having only one FK in table Tags, i.e. TargetId, which could beassociated with PostId, EventId or FileId ...This does seem right to me."I would try this option. |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-10-16 : 17:24:54
|
| In that case TargetId would have 3 Relationships:One to PostId, one to FileId and other to EventId.For example, when I create a Post record I would create N records in Tags, using PostId as TargetId.If I create a File record then the Tags records will contain that FileId.Is this possible?My Ids are Guid but, even so, will I not have problems with this?Thanks,Miguel |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 17:46:42
|
| No, just make sure that your TargetID field and your postID fields and the other fields are all the same datatype or have the same length (if using nvarchar or nchar or whatever). |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-10-16 : 17:47:55
|
| Hi,I think I will use the following:Posts (PostId PK)Files (FileId PK)Events (EventId PK) TagsInPosts (TagId PK, PostId PK) TagsInFiles (TagId PK, FileId PK) TagsInEvents (TagId PK, EventId PK)Tags (TagId PK) It seems the correct way to do something like this, right?Thanks,Miguel |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 17:51:28
|
| It looks like you changed from your original id.Just doPosts-->PostID nvarchar(50)Events-->EventID nvarchar(50)Files-->FileID nvarchar(50)Tags-->TagID nvarchar(50),TargetID nvarchar(50),TagName nvarchar(50) etc...In this scheme, each Tag has an id and that tag will be associated with a row from one of your parent tables. |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 17:55:46
|
| This assumes that each Post, Event, or File can have only one tag, though.I'm not exactly sure if you want that or a many to many relationship. |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-10-16 : 18:12:47
|
| Yes,I need an Post, File, ... to have more then one tag.That is why I think the latest option I posted is the best way to do this.Thanks,Miguel |
|
|
|
|
Relationships (FK). Need advice.