LINQ. Which tables relationships should I use?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shapper - 16 Oct 2007 22:17 GMT

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 .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

Reply to this Message

Frans Bouma [C# MVP] - 17 Oct 2007 09:51 GMT

> Hello,
>
[quoted text clipped - 22 lines]
> I need to extend my decision to categories, ratings, etc.
> So option 2 really seems bad idea.

    Actually, option 1 is the bad idea :). The thing is that the FK of
option 1 can't point to a PK table, so you actually have no referential
integrity checking.

    Having a tag-element table for each element is what you should do, as
it defines the relationship between tag and element, so option 2 is
better. Your initial idea of 1 table is actually a combination of all
tables resulting of option 2 into 1 table, but that has the downside
that if you want to add an element for tagging, it will force you to
update that table, instead of placing a new table into the db.

        FB

Signature

------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Reply to this Message

shapper - 17 Oct 2007 11:18 GMT

On Oct 17, 9:51 am, "Frans Bouma [C# MVP]"
<perseus.usenetNOS...@xs4all.nl> wrote:

> > Hello,
>
[quoted text clipped - 43 lines]
> Microsoft MVP (C#)
> ------------------------------------------------------------------------

Hi,

I ended up with the following structure:

[Posts] > PostId (PK), ...
[Files] > FileId (PK), ...
[Events] > EventId (PK), ...

[PostsTags] > PostId (PK), TagId (PK)
[FilesTags] > FileId (PK), TagId (PK)
[EventsTags] > EventId (PK), TagId (PK)

[Tags] > TagsId (PK), ...

I added the tables as this to LINQ (I used a dbml file)

What do you think?

I am starting to move all my projects from .NET 2.0 to .NET 3.5 using
VS 2008 Beta 2 and would appreciate advice on this.

Thanks,
Miguel

Reply to this Message

Frans Bouma [C# MVP] - 18 Oct 2007 11:57 GMT

> On Oct 17, 9:51 am, "Frans Bouma [C# MVP]"
> <perseus.usenetNOS...@xs4all.nl> wrote:
[quoted text clipped - 66 lines]
>
> What do you think?

Looks OK. If you now simply want all tags, you can, if you want all
posts based on one or more tags, you can etc. If you want to enable
other elements for tagging, you can too as it's very easy to add
support for that: just add a table.

FB

Signature

Reply to this Message

LINQ. Which tables relationships should I use?

Free Magazines