Can there be in whatever way to make use of inheritance in database (Particularly in SQL Server 2005)?
Suppose I've couple of area like CreatedOn, CreatedBy which I wish to add-on all my organizations. I searching for another way rather than adding these fields to each table.
PostgreSQL has this feature. Just add this towards the finish of the table definition:
INHERITS FROM (tablename[, othertable...])
The kid table may have all of the posts of their parent, and changes towards the parent table can change the kid. Also, my way through the kid table can come up in queries towards the parent table (automatically). Regrettably indices don't mix parentsOrkid border, that also means you cannot make certain that particular posts are unique across both parent and child.
So far as I understand, it isn't an element used very frequently.
There's no such factor as inheritance between tables in SQL Server 2005, so that as noted through the others, you will get so far as getting help adding the required posts towards the tables whenever you create them, however it will not be inheritance you may already know it.
Think about it a lot more like a template for the source code files.
As GateKiller mentions, you may create a table that contains the shared data and reference it having a foreign key, but you'll either need to have audit hooks, triggers, or perform the update by hand.
Main point here: Manual work.
You can produce a template within the template pane in Management Studio. After which use that template any time you are thinking about creating a brand new table.
Failing that, you can keep CreatedOn and CreatedBy fields within an Audit trail table referencing the initial table and id.
Failing that, get it done by hand.
You could utilize an information modeling tool for example ER/Studio or ERWin. Both tools have domain posts where one can define a column template that you could affect any table. Once the domain changes so the connected posts. ER/Studio also offers trigger templates that you could build and affect any table. This is the way we update our LastUpdatedBy and LastUpdatedDate posts without needing to build and keep 100s of trigger scripts.
Should you choose create an audit table you'd have one row for each row in each and every table that utilizes the audit table. That may get untidy. For me, you are best putting the audit posts in each and every table. Additionally you might want to put a timestamp column in most of the tables. Who knows when concurrency turns into a problem. Our DB audit posts that people place in every table are: CreatedDt, LastUpdatedBy, LastUpdatedDt and Timestamp.
Hope this can help.
There exists a SProc that contributes audit posts to some given table, and (optionally) produces a brief history table and connected triggers to trace changes to some value. Regrettably, company policy means I can not share, however it is not hard to achieve.
If you work with GUIDs you can produce a CreateHistory table with posts GUID, CreatedOn, CreatedBy. For inhabiting the table you'd still need to produce a trigger for each table or handle it within the application logic.
You don't want to make use of inheritance to get this done! When table B, C and D gets from table A, this means that querying table A provides you with records from B, C and D. Now consider...
Remove From the
Rather than inheritance, use LIKE rather...
CREATE TABLE blah ( blah_id serial PRIMARY KEY , something text NOT NULL , LIKE template_table INCLUDING DEFALUTS );
Ramesh - I'd implement this using supertype and subtype associations during my E-R model. You will find a couple of different physical options you've of applying the associations too.
in O-R mapping, inheritance maps to some parent table in which the parent and child tables make use of the same identifier
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 includes a foreign-key relationship to Object. whenever you produce a SubObject row, you have to first create an item row and employ the Id both in rows