How to design tables that map the same entity?

Given the two following tables :

Gallery

id | title | desc

Site

id | title | desc | url

I've开发者_C百科 a tags system which can apply to both Gallery and Site tables.

I'm wondering if I should do :

TagMap

tagId | entityId | applyTo

Where applyTo could be 'site' or 'gallery' or use separate table like the following :

TagGalleryMap

tagId | galleryId

and

TagSiteMap

tagId | siteId

What are you though about this ?

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.

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.

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

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