CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Overhed
Overhed

Reputation: 1314

Generic Resource Table Design Approach

I'm sure this is a duplicate question but I haven't found a suitable answer yet, so I figured I'd ask about this (I would think) very common database design problem:

I'm working on designing a SQL database which will have different record types that will contain images, so Tables A, and B, both of which house distinct record types, will have each of their rows linked to multiple image records.

I am trying to decide whether to take the approach of creating 1 generic table to house all images (let's call it "Image") or whether to create two distinct tables ("AImage" and "BImage") to house the images of each record type separately.

There will be a one to many relationship between A and its images and B and its images

If I create the generic Image table, it would have as columns: Id, ParentType (A or B, in this case), ParentId. and ImagePath. This approach seems cleaner (especially since tables "AImage" and "BImage" would be identical to each other), but I encounter issues almost immediately (like when trying to establish Foreign Key constraints). I should note that there may be other record types in the database that will need Images with the same One to Many relationship as Tables A & B.

So what is the right approach here?

Thanks.

Upvotes: 0

Views: 225

Answers (2)

Joel Brown
Joel Brown

Reputation: 14408

You should create two separate image tables, one for the (many) images that pertain to object type A and another for the (many) images that pertain to object type B. There are other ways to do it, but they aren't generally a good idea.

Just because two tables have similar columns doesn't mean that the tables are really the same table.

As you've realized, A_IMAGE and B_IMAGE are already different in one very important way, they have different foreign key columns and constraints.

Keeping similar looking tables distinct in this way can seem problematic to programmers, because it doesn't feel like good code reuse. However, the rules for structuring code to get best practice maintainability (e.g. OO) are different from the rules for structuring data to get best practice maintainability (e.g. normal forms).

If it makes you feel less like you need to take 10 showers: when you create the CRUD code for your A_IMAGE and B_IMAGE tables, have the aImageCrud and bImageCrud objects both inherit from a common imageCrud object so you get your code reuse without having to compromise your data model.

Upvotes: 1

DrabJay
DrabJay

Reputation: 3099

This is the classic Polymorphic Association anti-pattern. There are a number of possible solutions:

1) Exclusive Arcs e.g. for the Images table

Id  Image_Path       TableA_Id TableB_Id
1   /path/to/image1  1
2   /path/to/image2            1
3   /path/to/image3  2

Where both TableA_Id and TableB_Id are nullable and exactly one must be not null. Foreign keys can be declared on the TableA_Id and TableB_Id.

2) Reverse the Relationship e.g remove the Parent_Type and Parent_Id from the Images table and create two new tables

TableA_Images

Image_Id  TableA_Id
1         1
3         2

TableB_Images

Image_Id  TableB_Id
2         1

Foreign keys can be declared on the TableA_Id and TableB_Id.

3) Create a super-type table for TableA/TableB such as TableSuper and have the Images table reference the primary key of this new table.

You would need to decide which of these approaches you feel is most appropriate in your specific situation.

Upvotes: 1

Related Questions