August 3, 2016 at 9:59 am
I am working on a database design for a new project. The basic idea is that there is a unique object. This object can be different type, let's say we have computers and furniture. I need both of these item types to be in a single table with a unique identifier for reporting and tracking purposes. So this table would have a UUID, Object Name, Object Type, dateadded, etc. Just very basic information.
Now, I need detailed information for each of these objects, but obviously the details will be different.
For computers I would need fields for Memory Size, PC, Serial Number, etc.
For furniture I would need fields for Color, Dimensions, Furniture Type, etc.
A lot of times this data would be in separate tables, but I don't want separate tables for each item type. Any good examples or ideas?
Thanks!
August 3, 2016 at 12:20 pm
Quick thought, you can define "hard" attributes like weight, size, value etc. which are common to all and then use sparse columns in the same table for softer attributes which only apply to certain object types.
😎
August 5, 2016 at 1:29 pm
I think part of the problem with these kind of designs is that you can have cases where the logical model is fairly simple on paper but then it gets pretty gnarly when doing the physical implementation.
In your scenario, a lot of times people lean towards doing subtyping in some way. That's all well and good and make sense but the physical implementation can often lead to very complex queries. The other approach I've seen is using an EAV model of some sort. In a nutshell have a lookup table of attributes and then have the attribute IDs and respective values for the attributes of the objects, like those you can see in some of the bigger ERP type of applications. But having worked on both complex subtyping and ERPs with a ton of flexible attribute type of designs, they both give me a headache when I think about it. Both approaches are probably good for different scenarios but sometimes simpler approaches can be better. It really depends on the grand scheme of the project being worked on.
I just found a couple of basic examples of using both which might give you some ideas in those arenas to think about, some discussions about the pros and cons of those approaches as well:
EAV:
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx
Subtyping:
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply