StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This problem is pretty common, and I haven't seen a solution that is without drawbacks. The only option to exactly store a hierarchy of objects in a database is to use some NoSQL database.</p> <p>However, if a relational database is mandated, I usually go with this approach:</p> <ul> <li>one table for the base class/interface, that stores the common data for all types</li> <li>one table per descending class, that uses the exact same primary key from the base table (this is a nice use case for the SQL Server 2011 sequences, btw)</li> <li>one table that holds the types that will be stored</li> <li>a view that joins all those tables together, to enable easy loading/querying of the objects</li> </ul> <p>In your case, I would create:</p> <ul> <li>Table CommandBase <ul> <li>ID (int or guid) - the ID of the command</li> <li>TypeID (int) - ID of the type of command</li> </ul></li> <li>Table CommandA <ul> <li>ID (int or guid) - the same ID from the CommandBase table</li> <li>X (int)</li> <li>Y (int)</li> </ul></li> <li>Table CommandB <ul> <li>ID (int or guid) - the same ID from the CommandBase table</li> <li>Name (nvarchar)</li> </ul></li> <li>Table CommandTypes <ul> <li>ID (int) - ID of the command type</li> <li>Name (nvarchar) - Name of the command type ("CommandA", "CommandB",...)</li> <li>TableName (nvarchar) - Name of the table that stores the type - usefull if some dynamic sql is needed - otherwise optional</li> </ul></li> <li><p>View Commands, something along the lines of: </p> <pre><code>select cb.ID, a.X, a.Y, b.Name from CommandBase cb left outer join CommandA a on a.ID = cb.ID left outer join CommandB b on b.ID = cb.ID </code></pre></li> </ul> <p>The upside of this approach is that it mirrors your class structure. It's easy to understand and use.</p> <p>The downside is that is gets more and more cumbersome as you add new classes, it's hard to model more then one level of hierarchy, and the view can get a mile long if there are lots of descendants.</p> <p>Personally, I would use this approach if I know that the number of subclasses is relatively small and relatively fixed, as it requires creating (and maintaining) a new table for each new type. However, the model is quite simple, so it's possible to create a tool/script that could do the creating and maintaining for you.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload