ansaurus

Question

How to design tables that map the same entity ?

Answer 1

A:

I don't see anything terribly ugly with the former idea - (tagId, entityId, applyTo). It would also generalise to further domain objects to which you might wish to attach tags.

I don't know off-hand if using an applyTo int (or enum) column might be faster, and maybe a TagMapLabel table that maps the applyTo number to a string ('gallery', 'site', etc.) for rendering purposes.

frank

Frank Shearar 2010-03-07 14:08:15

Well, I see it somewhat ugly when foreign keys are not maintained for associations. It is a matter of taste, though.

incarnate 2010-03-07 14:36:09

Answer 2

+1 A:

Having two tables, TagGalleryMap and TagSiteMap, allows you to enforce foreign keys referencing the Gallery and Site tables. The one table solution doesn't allow this. Of course if you add a third master table (say Slideshow) you would need to add another matching intersection table, TagSlideshowMap.

APC 2010-03-07 14:09:06

Answer 3

A:

why dont you make one table with these fields:

id | title | desc | url | type

and in the type column, list either "Gallery" or "Site"

then you could create your tag table like this:

TagMap
tagId | entityId | applyTo

CheeseConQueso 2010-03-07 14:11:29

Mostly because the given data are just for example, Site and Gallery tables are more complex, and I don't think fit them both into the same table will be good either for performance and/or management

John 2010-03-07 14:13:49

yeah i guess that depends on your situation.... put some of your real data up so that it can be answered better

CheeseConQueso 2010-03-07 14:19:15

Answer 4

+1 A:

Both solutions are valid, so it comes down to is respective advantages and disadvantages:

Method 1

(+) only one table to maintain
(-) the key is more complex (two fields instead of one)
(-) no out of the box foreign key checking (can be implemented by triggers though)
(-) no cascading deletes (can be implemented by triggers though)
(+) queries that do not care about one type or the other are easier to implement
(-) queries that do care about the type need additional predicates
(+) the mapping between the object and the table is straight forward, inheritance can be implemented in the load and save routines
(+) additional types can be included without modifying the database
(+) navigation from the tags to the objects is easier than with the second method, as there is only one table you habe to look in.

Method 2

(-) two tables to maintain
(+) simple key that has a clear relationsip with its parent
(+) foreign key checking is simple
(+) cascading deletes work directly
(-) queries that do not care about one type or the other more difficult as they have to take multiple tables into account
(+) queries that do care about the type are easier
(-) the mapping between the object and the tables is not straigt forward, inherited objects need to pull the data from different tables
(-) additional types require additonal tables, therefore, modifications of the database
(-) navigation from the tags to the objects is more difficult than with the firstmethod, as there are multiple methods to look in (this can be leviated with views, but additional tables mean that you have to change the view too).

Edit:

Method 2 works should work with all ORM mappers as it is a simple parent-child relationship.

Method 1 needs to be supported by an ORM mapper. Some ORM mappers support such constructs by the notion of inheritance (e.g. Linq to SQL, .Net Entity Framework). If inheritance is supported you have a base type (the table) and inheriting types. Each inheriting type needs to have the information that discriminates it from the other types, based on the information provided by the data in the table. This discriminator is stored with the inheriting type and used to create the "filtered" queries needed. When providing inheritance eager loading is mostly also supported for these types (again e.g. the two named ORMs).

Note: I used Linq to SQL and the .NET Entity Framework as an example because I know them best, but I believe that other ORM have similar concepts.

Obalix 2010-03-07 14:20:38

If the author intends to use some sort of ORM or any other kind of middleware, then he should forget about #1. None of the tools have support for such "query metadata" inside tables.And you forgot to mention, that eager fetching strategies will not apply to #1 -- you'll be forced to select tags first, then look into `applyTo` and then select again; while with #2 it is possible to fetch tags with their associated sites and/or galleries in one query.

incarnate 2010-03-07 14:34:02

@incarnate: Some ORM mappers allow the notion of inheritance out of a single table based upon a discriminator inside the table (e.g. Linq-to-SQL, .NET Entity Framework) - in our case the discriminator would be "AppliesTo". The availability for eager fetching depends on the "intelligence" of the ORM mapper, again e.g. Linq to SQL can do this for inherited types. To achive the inheritance an ORM mapper, Linq to SQL for example, stores the discrimiator with the type inheriting from the table base type. All generated queries now can take the descriminator into account.

Obalix 2010-03-07 14:42:00

The term "inheritance" in that case would imply that there would be `SiteTag` and `GalleryTag` entities with some superclass -- that's exactly what method #2 states about (except for little overhead -- just look at how mappers deal with table-per-concrete-class inheritance mapping strategy).

incarnate 2010-03-09 08:32:16

ansaurus

tags:

views:

answers:

How to design tables that map the same entity ?

related questions