Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
s.parc
Starting Member
3 Posts |
 Posted - 2010-06-29 : 08:58:33
|
| Dynamic supertype/subtypeWithin database modelling there is a real need to represent data in supertype and subtype models. Supertype tables contain columns – attributes common for an object - data class. Subtype tables contain columns – attributes specific for a subobject – data subclass. A good designer will, exploring the real world, endeavour to predict every subobject with all their attributes. However, in the real world, the same object can be considered from different viewpoints. In sense of terms this object is considered equally by everyone, whereas what makes them different are the properties considered for the given object.The idea lies in overcoming these differences by forming a model that would resolve these peculiarities of use.To simplify the concept, I am going to use a simplified data model for fixed assets. Fixed assets are non-current assets of an enterprise. Depending on the branch of industry it belongs to, they can be considered from different viewpoints and it is necessary to provide a different set of attributes.Master Data for Fixed Assets are a typical example for supertype/subtype objects.Master Data Table may include the following fields:- Id PK- Name - .....- Type (in ‘B’,’M’,’V’) B=Buildings, M=Machines, V=VehiclesThe Buildings Table „B“ may have the following attributes:- Id PK (FK)- Type default = ’B’- Number of floors - Area - Wall materials - ........The Machine Table may contain the following attributes-columns-fields:- Id PK (FK)- Type default = ’M’- Power- Capacity- Number of working hours - ........The Vehicles Table may contain the following fields:- Id PK (FK)- Type default =’V’- Engine Number - VIN / Chassis Number - .........In the above model the number of types is limited (static), so this problem can be resolved by introducing a table of types to be referenced to by the Type field.However, the problem is in a lack of subtables for new types, as they haven't been designed, and also attributes for the new tables are missing. The attributes that we would like to store in certain subtypes can be enlisted in the Type table.My question is: Can we resolve this issue using SQL Server 2008 and how? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-29 : 10:45:50
|
| I don't know what you are really after, but the attributes for different "Types" are stored in metadata for the Building, Machine, and Vehicle tables.CODO ERGO SUM |
 |
|
|
s.parc
Starting Member
3 Posts |
Posted - 2010-06-29 : 13:36:23
|
| Diferent "Type" ask diferent atributes, this atribytes are not in metadata for existing types. For example, new type can be computers, for them is necessary to specify the following attributes: procesor, RAM, hdd capaity.... In the Master Data table we can add new types of resources type C computers and field "Type" reference to the table "AssetsTypes". However, lack of tables for computers with their attributes. I hope I was clear now.AssetsTypes:-Type PK-Name C ComputersB BuldingsM MahinesV Vehicles |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-29 : 13:57:01
|
| I am afraid I don’t really understand what you are after.What is wrong with simply creating a new table, like Computers, when you come up with a new type? Why would that not work?CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-06-29 : 14:15:23
|
OKquote:
Supertype tables contain columns – attributes common for an object - data class. Subtype tables contain columns – attributes specific for a subobject – data subclass.
Going with that...ummm...probably some bone-heads idea to add more buzz words...to en ever increasing lexicon of buzz words...A Parent_Table may have 0 to many rows in a Child TableAlso A Parent table, may have 0 to many Children TablesDoneNext questionBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-29 : 15:06:50
|
| The problem you are describing is a classic example of the impedance mismatch between relational data and object oriented design. The fact is that relational structures are meant to be relatively fixed in structure; they store sets of the same object, not types of objects.You can perhaps achieve what you're after in PostGreSQL using table inheritance:http://www.postgresql.org/docs/8.1/static/ddl-inherit.htmlHowever SQL Server does not have such a feature, so you'll either have to use parent-child tables with regular joins and foreign keys, entity-attribute-value tables, or possibly XML columns. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Dynamic supertype/subtype