StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are several ways to model inheritance in a database. Which you choose depends on your needs. Here are a few options:</p> <p><strong>Table-Per-Type (TPT)</strong></p> <p>Each class has its own table. The base class has all the base class elements in it, and each class which derives from it has its own table, with a primary key which is also a foreign key to the base class table; the derived table's class contains only the different elements.</p> <p>So for example:</p> <pre><code>class Person { public int ID; public string FirstName; public string LastName; } class Employee : Person { public DateTime StartDate; } </code></pre> <p>Would result in tables like:</p> <pre><code>table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK, FK) datetime startdate </code></pre> <p><strong>Table-Per-Hierarchy (TPH)</strong></p> <p>There is a single table which represents all the inheritance hierarchy, which means several of the columns will probably be sparse. A discriminator column is added which tells the system what type of row this is.</p> <p>Given the classes above, you end up with this table:</p> <pre><code>table Person ------------ int id (PK) int rowtype (0 = "Person", 1 = "Employee") string firstname string lastname datetime startdate </code></pre> <p>For any rows which are rowtype 0 (Person), the startdate will always be null.</p> <p><strong>Table-Per-Concrete (TPC)</strong></p> <p>Each class has its own fully formed table with no references off to any other tables.</p> <p>Given the classes above, you end up with these tables:</p> <pre><code>table Person ------------ int id (PK) string firstname string lastname table Employee -------------- int id (PK) string firstname string lastname datetime startdate </code></pre>

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