CODEWITHSUNDEEP
sqldatabaseinheritancesql-server-2005
Ramesh Soni
Ramesh Soni

Reputation: 16077

Inheritance in database?

Is there any way to use inheritance in database (Specifically in SQL Server 2005)?

Suppose I have few field like CreatedOn, CreatedBy which I want to add on all of my entities. I looking for an alternative way instead of adding these fields to every table.

Upvotes: 9

Views: 5078

Answers (9)

Steven A. Lowe
Steven A. Lowe

Reputation: 61242

in O-R mapping, inheritance maps to a parent table where the parent and child tables use the same identifier

for example

create table Object (
    Id int NOT NULL --primary key, auto-increment
    Name varchar(32)
)
create table SubObject (
    Id int NOT NULL  --primary key and also foreign key to Object
    Description varchar(32)
)

SubObject has a foreign-key relationship to Object. when you create a SubObject row, you must first create an Object row and use the Id in both rows

Upvotes: 0

Mike McAllister
Mike McAllister

Reputation: 1549

Ramesh - I would implement this using supertype and subtype relationships in my E-R model. There are a few different physical options you have of implementing the relationships as well.

Upvotes: 0

decibel
decibel

Reputation: 421

You do NOT want to use inheritance to do this! When table B, C and D inherits from table A, that means that querying table A will give you records from B, C and D. Now consider...

DELETE FROM a;

Instead of inheritance, use LIKE instead...

CREATE TABLE blah (
    blah_id     serial       PRIMARY KEY
    , something text         NOT NULL
    , LIKE template_table    INCLUDING DEFALUTS
);

Upvotes: 0

Rowan
Rowan

Reputation: 478

If you are using GUIDs you could create a CreateHistory table with columns GUID, CreatedOn, CreatedBy. For populating the table you would still have to create a trigger for every table or handle it in the application logic.

Upvotes: 0

ZombieSheep
ZombieSheep

Reputation: 29963

We have a SProc that adds audit columns to a given table, and (optionally) creates a history table and associated triggers to track changes to a value. Unfortunately, company policy means I can't share, but it really isn't difficult to achieve.

Upvotes: 0

Jorriss
Jorriss

Reputation: 1083

You could use a data modeling tool such as ER/Studio or ERWin. Both tools have domain columns where you can define a column template that you can apply to any table. When the domain changes so do the associated columns. ER/Studio also has trigger templates that you can build and apply to any table. This is how we update our LastUpdatedBy and LastUpdatedDate columns without having to build and maintain hundreds of trigger scripts.

If you do create an audit table you would have one row for every row in every table that uses the audit table. That could get messy. In my opinion, you're better off putting the audit columns in every table. You also may want to put a timestamp column in all of your tables. You never know when concurrency becomes a problem. Our DB audit columns that we put in every table are: CreatedDt, LastUpdatedBy, LastUpdatedDt and Timestamp.

Hope this helps.

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391664

There is no such thing as inheritance between tables in SQL Server 2005, and as noted by the others, you can get as far as getting help adding the necessary columns to the tables when you create them, but it won't be inheritance as you know it.

Think of it more like a template for your source code files.

As GateKiller mentions, you can create a table containing the shared data and reference it with a foreign key, but you'll either have to have audit hooks, triggers, or do the update manually.

Bottom line: Manual work.

Upvotes: 3

Neall
Neall

Reputation: 27154

PostgreSQL has this feature. Just add this to the end of your table definition:

INHERITS FROM (tablename[, othertable...])

The child table will have all the columns of its parent, and changes to the parent table will change the child. Also, everything in the child table will come up in queries to the parent table (by default). Unfortunately indices don't cross the parent/child border, which also means you can't make sure that certain columns are unique across both the parent and child.

As far as I know, it's not a feature used very often.

Upvotes: 2

GateKiller
GateKiller

Reputation: 75969

You could create a template in the template pane in Management Studio. And then use that template every time you want to create a new table.

Failing that, you could store the CreatedOn and CreatedBy fields in an Audit trail table referencing the original table and id.

Failing that, do it manually.

Upvotes: 0

Related Questions