Asked  7 Months ago    Answers:  5   Viewed   36 times

What are the best practices for modeling inheritance in databases?

What are the trade-offs (e.g. queriability)?

(I'm most interested in SQL Server and .NET, but I also want to understand how other platforms address this issue.)



There are several ways to model inheritance in a database. Which you choose depends on your needs. Here are a few options:

Table-Per-Type (TPT)

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.

So for example:

class Person {
    public int ID;
    public string FirstName;
    public string LastName;

class Employee : Person {
    public DateTime StartDate;

Would result in tables like:

table Person
int id (PK)
string firstname
string lastname

table Employee
int id (PK, FK)
datetime startdate

Table-Per-Hierarchy (TPH)

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.

Given the classes above, you end up with this table:

table Person
int id (PK)
int rowtype (0 = "Person", 1 = "Employee")
string firstname
string lastname
datetime startdate

For any rows which are rowtype 0 (Person), the startdate will always be null.

Table-Per-Concrete (TPC)

Each class has its own fully formed table with no references off to any other tables.

Given the classes above, you end up with these tables:

table Person
int id (PK)
string firstname
string lastname

table Employee
int id (PK)
string firstname
string lastname
datetime startdate
Tuesday, June 1, 2021
answered 7 Months ago

You can run the following script, just change the @path variable to where you want to store the databases.

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:Backup'  


SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor

Obtained from:

Wednesday, August 18, 2021
Youssif Saeed
answered 4 Months ago

OK, the answer now definitively is:

The SqlCommandBuilder is trying to be "intelligent". If you open it with a command like SELECT * FROM vwTest, then it analyzes the view and creates commands for the underlying table, like INSERT into tblTest ...

So the problem is: SqlCommandBuilder creates commands for the underlying table, instead of for the view.


So far, I found no way to change the behaviour of SqlCommandBuilder.

Therefore, I rewrote all the loading and updating, and I am doing now everything manually. Loading now takes place purely with SqlDataReader -- without loading into a DataTable with SqlDataAdapter. And all updating is done via creating and executing SqlCommand, without SqlCommandBuilder.

It was a lot of work, but as a reward, the application is now blazing fast. Loading is far faster than with the SqlCommandBuilder and SqlDataAdapter. Possibly I will make a benchmark comparison at some time. But when a load took 5 seconds before, it is now done "immediatedly".

Wednesday, August 25, 2021
answered 4 Months ago

How about the Materialized Path design?

  path VARCHAR(<maxdepth>) PRIMARY KEY,
  ...other attributes of a tree node...

To store a word like "stackoverflow":

  ('s'), ('st'), ('sta'), ('stac'), ('stack'),
  ('stacko'), ('stackov'), ('stackove'), ('stackover'),
  ('stackover'), ('stackoverf'), ('stackoverflo'),

The materialized path in the tree is the prefixed sequence of characters itself. This also forms the primary key. The size of the varchar column is the maximum depth of trie you want to store.

I can't think of anything more simple and straightforward than that, and it preserves efficient string storage and searching.

Thursday, September 9, 2021
answered 3 Months ago

Well, you could do something like this:


IdList  IdPick  Text  
1       1       Apples
1       2       Oranges
1       3       Pears
2       1       Dogs
2       2       Cats

and optionally..


Id    Description
1     Fruit
2     Pets
Thursday, November 25, 2021
Jeremy J Starcher
answered 1 Week ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :