Is there a table inheritance in SQL ?

I need to make two tables which are identical :

1. Recharcheable battery

2. Primery Battery

Is there a way to make a 'Battery' Table and create the 1 and 2 tables with inheritance in a way that when i change a field from smallint to int in the Battery Table , The same fields in the 1 and 2 tables will be replaced too?

(I work with SQL Server 2005)

thanks.

Why would you want to duplicate the columns in the inherited tables?  It sounds to me like what you really might want to do is create a view (or two), which has all of the columns from each table.  But your base tables probably shouldn't duplicate.   One method of doing inheritance is to set up a column in the base table that defines the inherited type:   CREATE TABLE Batteries (     BatteryId INT NOT NULL PRIMARY KEY,     BatteryType CHAR(1) NOT NULL,     ... (other common battery columns),     CONSTRAINT UN_Battery_BatteryType UNIQUE (BatteryId, BatteryType) )   Now (BatteryId, BatteryType) is a candidate key, so you can use it for RI in other tables...   CREATE TABLE RechargeableBatteries (     BatteryId INT NOT NULL PRIMARY KEY,     BatteryType CHAR(1) NOT NULL,     ... (Rechargeable battery columns),     CONSTRAINT FK_RechargeableBatteries FOREIGN KEY (BatteryId, BatteryType) REFERENCES Batteries (BatteryId, BatteryType),     CONSTRAINT CK_RechargeableBatteries CHECK (BatteryType = 'R') )   ... The FK makes sure that you get both columns from the Batteries table together -- a BatteryId is tied to its BatteryType.  The CHECK constraint ensures that this "RechargeableBatteries" table only has rows for rechargeable batteries.   Once you have all of your tables set up, you can create views with all of the columns, if you want everything to be a bit more uniform (and don't want to deal with joining back to the base table every time).  
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--    

<rodniko@discussions.microsoft.com> wrote in message news:1b58c9e9-d20b-4da5-b5fb-23eae5ce02a9@discussions.microsoft.com...

Is there a table inheritance in SQL ?

I need to make two tables which are identical :

1. Recharcheable battery

2. Primery Battery

Is there a way to make a 'Battery' Table and create the 1 and 2 tables with inheritance in a way that when i change a field from smallint to int in the Battery Table , The same fields in the 1 and 2 tables will be replaced too?

(I work with SQL Server 2005)

thanks.

hi,

As a matter of rule,

normalization belong to the database while

inheritance belongs to the front end.

you should have a normalize table

in its normal form you shall have two tables,

one is the "battery" the other is the "battery type"

 

if you want to break the rules and proceed with your hearts desire

you can do so by using DDL (Datat definition language) triggers.

 

regards,

joey

 

You should probably read Date's "Introduction to Database Systems" for some background in modeling inheritance.  It certainly has a place in the database -- and the Battery example is probably a pretty good one.  What if the different types of batteries share some attributes (volts, for instance), but have various different attributes depending on type, that they don't share?  For instance, amount of time to recharge from a fully discharged state.  That attribute makes no sense for non-rechargeable batteries.  A NULL column could be used, but that may not make sense either.  Do you want to carry the extra attribute(s) along for every row?  It's often cleaner to do a subtype-supertype model and put typed attributes in a separate table.  
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--    

<joeydj@discussions.microsoft.com> wrote in message news:7e50679e-8b8b-403f-be6c-2d1189c9688b_WBRev1_@discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com

hi,

As a matter of rule,

normalization belong to the database while

inheritance belongs to the front end.

you should have a normalize table

in its normal form you shall have two tables,

one is the "battery" the other is the "battery type"

 

if you want to break the rules and proceed with your hearts desire

you can do so by using DDL (Datat definition language) triggers.

 

regards,

joey

 

i guess i'll have to do some serious reading....

Thanks guys you've helped me alot!!

Hi there

let me put my question in short. Does MS-SQL 2005 support table inheritance just like the following link shown http://www.sql.org/sql-database/postgresql/manual/tutorial-inheritance.html. If not, what is the microsoft way of doing it. Thanks !

thanks for your help!

The answer "in short" is "no". MS SQL Server is not an object-oriented database. Note that in the link you reference that the writer uses a VIEW to fetch the data. That is also one of MVP's suggestions in the thread you grabbed. The real answer to your question for the "SQL Server way" is that it depends on exactly what you are trying to do. You might want to create a new post and give specifics of what you are trying to do. Also, welcome to the forum.

Also, give some look in the concept of subcategory relationships in SQL Server; that might pay some dividends in this particular case.

Hi,

Recently in my companywe had a similar requirement. I thought this requirement can be fulfilled by using the DDL (Data Definition Language) triggers.

So I started withcreating aDDL trigger to synchronize two tables when a structure change occurs.

I placed the code I had created to my site www.kodyaz.com to Files section.

Hereis where you candownload the ddl trigger titled DDL Trigger for ALTER_TABLE to Synchronize Tables

This script keeps changes on newly added columns on base tables, and adds the newly added column to its log table.

Eralper

I would personally consider the open / close principle in OOP and apply it to the DB in a similar way. It might break some normalisation rules (im not sure) but it goes like this.

Create a battery table with the fields you want that are common to the other tables.

Next create a new table for each sub-type of battery.

Then link each sub table with the base table using a one to one relationship.

By specifying the relationship cascading rules right, you will not have to use triggers or views so all your code is obvious.

It does mean you will have to use joins to get at all the data but I dont see that as breaking any big bad rules. To me its logical as two tables are involved, so you create a join when needed.

Note: Im not suggesting there is anything wrong with triggers or views approach as recommended here. I just wanted to put an alternative forward.

I would welcome any suggestions on the pros and cons of this over other ideas as I am learning too. Thanks.

• Edited by franasmMSDN 16 hours 48 minutes ago

Look up the design flaw called "attribute splitting"  and avoid it. It is when you pick an attribute  and make its values into tables. Your would the guy with "Male_Personnel" and "Female_Personnel" table instead a Personnel table with a sex_code column.  

Then if you were a truly awful programmer, you would kludge it with TRIGGERs or worse! Yes, people do that :(

You have confused OO and RDBMS. This like trying to speak English using Korean verbs.