LINQ. Which tables relationships should I use?

[shapper]

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

[Frans Bouma [C# MVP]]

shapper wrote:

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.

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

--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------

[shapper]

On Oct 17, 9:51 am, "Frans Bouma [C# MVP]"
<[e-mail]:

shapper wrote:

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.

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

--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------

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

[Frans Bouma [C# MVP]]

shapper wrote:

On Oct 17, 9:51 am, "Frans Bouma [C# MVP]"
<[e-mail]:

shapper wrote:

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.

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

--
--------------------------------------------------------------------
---- 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#)
--------------------------------------------------------------------
----

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?

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
--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------