StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>How do you have the listings represented in code? I'd guess <code>Listing</code> as a supertype, with <code>Shop</code>, <code>Restuarant</code>, etc. as subtypes?</p> <p>Assuming so, this is a case of how to map subtypes to a relational database. There are generally three choices:</p> <ul> <li>Option 1: single table per subtype, with common attributes repeated in each table (name, id, etc).</li> <li>Option 2: single table for all objects (your single table approach)</li> <li>Option 3: table for the supertype and one for each subtype</li> </ul> <p>There's no universally correct solution. My preference is generally to start with option 3; it provides an intituitive structure to work with, is pretty well normalised and can easily be extended. It means a single join for retrieving each instance - but RDBMS are well optimised for doing joins so it doesn't really cause performance problems in practice.</p> <p>Option 2 can be more performant for queries (no joins) but causes problems if other tables need to refer to all supertype instances (proliferation of foreign keys). </p> <p>Option 1 appears at first sight to be the most performant, although 2 caveats: (1) It's not resilient to change. If you add a new subtype (and so different attributes) you'll need to change the table structure and migrate it. (2) It can be less efficient than it seems. Because the table population is sparse, some DBs don't store it particularly efficiently. As a consequence it can be less efficicent than option 1 - since the query engine can do joins faster than it can search bloated sparse table spaces.</p> <p>Which to choose really comes down to knowing details of your problem. I'd suggest reading up a bit on the options: <a href="http://www.agiledata.org/essays/mappingObjects.html#MappingInheritance" rel="nofollow">this article</a> is a good place to start.</p> <p>hth</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