[SQL] Overerving?

[Zyphrax]

Tijdens het ontwerpen van een database ben ik op het volgende probleem gestuit:
Onderstaande zou ik graag in database vorm willen realiseren.



Binnen het systeem kunnen meerdere typen objecten bestaan, denk hierbij aan Files, Folders of Users. Elk type object heeft zijn eigen specifieke attributen, er bestaan echter ook een aantal attributen die voor elk object bestaan. Die attributen zijn gebundeld in de tabel Object.

In programmeercode zou dit vrij makkelijk te realiseren zijn . Je zou een klasse Object maken met een aantal attributen en methoden. Daarnaast zou je een klasse File, Folder en User maken die Object "extenden" en hun eigen attributen en methoden hebben.

Ik wil deze informatie in een database opslaan, hoe kan ik dit op een verantwoorde manier in een SQL Server 2000 proppen?

De database zal geraadpleegt worden met behulp van C# (.NET), wellicht biedt dit nieuwe mogelijkheden?

Alvast bedankt.

[ Voor 7% gewijzigd door Zyphrax op 02-03-2004 13:08 ]

[Banpei]

Meerdere tabellen maken en dan aan de hand van je object joinen?

Dus dan krijg je in je tabel object een object_type waar je dan aan de hand daarvan een join uitvoert op tabel file, folder en user. Volgens mij moet SQL Server dit wel kunnen via een subselect.

[raoulduke]

Heeft jouw SQL server niet een functie gelijk aan die van INHERITS van PostgreSQL, zie http://www.postgresql.org...atic/sql-createtable.html.

[EfBe]

Vooropgesteld: dit is niet te modelleren in een relational model zonder checkconstraints of andere narigheid.

3 manieren:
1) flatten supertype/subtype. Dit houdt in dat je 1 tabel maakt met alle attributes van alle typen. Welke specifiek bij 1 type horen maak je nullable. Je voegt 1 extra column toe, met het type van je row, dus bv ObjectType, welk een nummer bevat bv (of een GUID). Dit is qua performance verreweg de snelste methode.
2) one table per subtype. Je maakt voor elk type een aparte table aan met daarin alle attributes van de parent + de unieke attributes van de child. In feite heb je dan geen inheritance in je model, gewoon meerdere entities. Dit model is erg naar, want je krijgt ellende wanneer een entity meerdere gedaanten kan hebben (employee die ook klant is bv), of wanneer bv een clerk promoveert naar manager.
3) one table per type, derived type tables contain only unique properties. Je hebt dus in jouw model 4 tables, waarbij file, folder en user dezelfde PK hebben als Object, en waarbij je dus een FK legt vanaf die PK's naar de PK van object (maakt het tot een 1:1 relatie). De 'type' tables bevatten alleen de unieke attributes van het type, de gedeelde attributes (van object) zitten in de object table.

3) is het meest robuust. 1) verreweg het snelst en 2) niet zo handig

Hou er rekening mee dat je erg goed moet opletten wat je doet. Bij alle varianten is het mogelijk inheritance te 'breken' door data in de database te veranderen, bij 3) het moeilijkst (pk value wijzigen), bij 1) het makkelijkst (nummertje wijzigen).

O/R mappers ondersteunen meestal (1 van of meerdere van) deze 3. Er is nog een 4e, met een hulptable per is-a relatie, ik zou je die niet aanraden.

Ik weet dus niet wat je onder 'verantwoorde' manier verstaat. Als dat inhoudt: moet altijd 100% correct zijn, vergeet dan supertypes/subtypes, want ze zijn altijd te breken en kies dan dus voor losse entiteiten.

[EfBe]

raoulduke schreef op 02 maart 2004 @ 13:13:
Heeft jouw SQL server niet een functie gelijk aan die van INHERITS van PostgreSQL, zie http://www.postgresql.org...atic/sql-createtable.html.

INHERITS is column definition copy, geen inheritance.

[whoami]

EfBe schreef op 02 maart 2004 @ 13:19:
Vooropgesteld: dit is niet te modelleren in een relational model zonder checkconstraints of andere narigheid.

3 manieren

Heb je toch in dat boek van Fowler gelezen?

[EfBe]

whoami schreef op 02 maart 2004 @ 13:29:
Heb je toch in dat boek van Fowler gelezen?

hehe nee, eigen research Voor wanneer ik het zelf moet gaan implementeren in LLBLGen Pro later dit jaar (manier 2 en 3)

[ Voor 4% gewijzigd door EfBe op 02-03-2004 13:30 ]

[raoulduke]

EfBe schreef op 02 maart 2004 @ 13:20:
[...]

INHERITS is column definition copy, geen inheritance.

Maar ik begrijp uit de werking van INHERITS (pgsql manual) dat je ermee een parent tabel uitbreidt met extra kolommen. Je kopieert geen kolommen / definities. Dit is volgens mij precies wat de TS wil: je hebt zeg maar een supertype (parent tabel) dat je kan extenden met subtypen (de tabel die INHERITS heeft op de parent tabel).

[whoami]

Inheritance is eigenlijk wel meer dan het 'extenden' van een hoop gegevens met extra velden.

Met inheritance bepaal je een 'is a' structuur. Het wil dus niet perse zeggen dat class A en class B , die beiden van class C inheriten een aantal gemeenschappelijke velden zouden hebben die door class C geintroduceerd werden.
Met inheritance bepaal je voornamelijk een 'contract' waar een class moet aan voldoen. Je kan natuurlijk ook gaan 'extenden', maar dat is niet altijd het geval.

[Zyphrax]

Thnx voor de suggesties.

Zelf zat ik ook al een beetje aan dit soort oplossingen te denken.
Een echt mooie oplossing komt er helaas niet uitrollen, er is altijd wel een mate van integriteit verlies.

Ik zou nog een stap verder kunnen brengen, door het volledig dynamisch te maken. Hierbij zou je de properties van een object in een property table kunnen stoppen. Nadeel hiervan is dat het ophalen van de data vrij complex wordt, veel joins vereist, daarnaast is de koppeling vanuit de programmeercode (C#) bijna niet te doen omdat je niet weet wat je moet verwachten voor properties. Het wordt bijv. dan bijna onmogelijk om een wachtwoord te checken omdat je niet van te voren weet dat het object-type user een wachtwoord heeft en hoe dit attribuut in de database heet.

[raoulduke]

Maar subclasses en superclasses zijn toch analoog aan een IS A structuur. Als we de TS zijn schema pakken, dan geldt (ik gebruik IS A, bij object zou eigenlijk IS AN moeten staan):

File IS A Object
Folder IS A Object
User IS A Object

Deze relatie kan je met het pgsql INHERITANCE keyword perfect implementeren zonder zelf extra constraints bij te moeten houden. Als voorbeeld:

code:

CREATE TABLE Object (id int, naam char(32));
CREATE TABLE File (pad char(80)) INHERITS(Object);
CREATE TABLE Folder(icon char(80)) INHERITS(Object);
CREATE TABLE User(username char(64)) INHERITS(Object);

Edit:
whoami, ik denk dat ik weet wat je bedoelt, maar met deze inherits-structuur die ik gaf, weet je zeker dat de User-tabel alle kolomdefinities heeft van de Object-tabel inclusief zijn eigen kolommen. Dit is dan toch op een manier ook het contract dat vastgelegd is? De kolommen uit Object zijn per definitie vastgelegd in de tabellen User, File en Folder. Net zoals dat in een subclass alle methoden van een superclass aanwezig zijn (al dan niet overridden).

[ Voor 28% gewijzigd door raoulduke op 02-03-2004 13:45 ]

[Zyphrax]

Is die PostgreSQL INHERITS constructie ook in MS SQL Server 2000 op de een of andere manier te realiseren?

Mocht dit niet kunnen waar kan ik dan een goede Windows PostgreSQL Server vinden?

[raoulduke]

Zyphrax, voor zover ik weet draait PostgreSQL het beste onder Unix / Linux. Er zijn oplossingen die Cygwin / MinGW gebruikten om het aan de praat te krijgen. Als je even zoekt in de mailing list archieven van PostgreSQL kan je meer informatie hierover vinden. Volgens mij staat een native win32 port best hoog op het programma, in ieder geval was er veel discussie over in de tijd (rond 7.3) dat ik subscribed was op de -devel mailinglist.

Edit:
Meer info over win32 postgres staat op: http://momjian.postgresql.org/main/writings/pgsql/win32.html

[ Voor 14% gewijzigd door raoulduke op 02-03-2004 13:57 ]

[Zyphrax]

Helaas ben ik enigzins beperkt door de eisen van mn opdrachtgever en denk ik zelfs dat ik PostgreSQL er niet doorheen krijg.

Misschien moet ik van objecten af stappen en het vrij statisch oplossen of ik moet het bijna volledig dynamisch maken, maar dan is de koppeling naar de code vrij onhandelbaar. Of een tussenoplossing met kans op integriteitverlies.

Bedankt iig!

[EfBe]

raoulduke schreef op 02 maart 2004 @ 13:42:
Maar subclasses en superclasses zijn toch analoog aan een IS A structuur. Als we de TS zijn schema pakken, dan geldt (ik gebruik IS A, bij object zou eigenlijk IS AN moeten staan):

File IS A Object
Folder IS A Object
User IS A Object

Deze relatie kan je met het pgsql INHERITANCE keyword perfect implementeren zonder zelf extra constraints bij te moeten houden. Als voorbeeld:

code:

CREATE TABLE Object (id int, naam char(32));
CREATE TABLE File (pad char(80)) INHERITS(Object);
CREATE TABLE Folder(icon char(80)) INHERITS(Object);
CREATE TABLE User(username char(64)) INHERITS(Object);

Edit:
whoami, ik denk dat ik weet wat je bedoelt, maar met deze inherits-structuur die ik gaf, weet je zeker dat de User-tabel alle kolomdefinities heeft van de Object-tabel inclusief zijn eigen kolommen. Dit is dan toch op een manier ook het contract dat vastgelegd is? De kolommen uit Object zijn per definitie vastgelegd in de tabellen User, File en Folder. Net zoals dat in een subclass alle methoden van een superclass aanwezig zijn (al dan niet overridden).

En als Object nu een extra column krijgt, ziet File die extra column dan ook meteen of niet? (vraag, ik ken postgresql verder niet). Ik vermoedde dus dat op het tijdstip van creatie van file, folder en user, de columndefinities van object worden 'gecopieerd' in file, folder en user. Als je daarna object aanpast door een extra column erbij te plaatsen, moeten file, folder en user die dus ook hebben, maar krijgen ze die ook?

(waar ik op doel is dus: als ik SELECT * FROM File doe, is dat dan onder water gelijk aan SELECT * FROM File inner join Object on File.PK = Object.PK ?)

[raoulduke]

Ik zal het gelijk even proberen voor u en alle anderen (ik heb even snel de SQL statements ingeklopt in een testdatabase 'inh'):

code:

inh=# create table object(id int, naam char(32));
CREATE TABLE
inh=# create table file(pad char(80)) inherits(object);
CREATE TABLE
inh=# alter table object add omschrijving char(64);
ALTER TABLE
inh=# \d object
          Table "public.object"
    Column    |     Type      | Modifiers
--------------+---------------+-----------
 id           | integer       |
 naam         | character(32) |
 omschrijving | character(64) |

inh=# \d file
           Table "public.file"
    Column    |     Type      | Modifiers
--------------+---------------+-----------
 id           | integer       |
 naam         | character(32) |
 pad          | character(80) |
 omschrijving | character(64) |

De kolommen die bij Object worden toegevoegd komen dus ook bij File erbij. Daarom ben ik er sterk van overtuigd dat dit een nette oplossing is voor een super-/subclass oplossing.

Conclusie: zoals jij het noemt klopt, onder water wordt alles netjes afgehandeld zonder dubbele waardes en/of kopieën. Sterker nog: PostgreSQL ondersteunt zelfs multiple inheritance (dus op hetzelfde niveau).

[ Voor 14% gewijzigd door raoulduke op 02-03-2004 15:00 ]

[Zyphrax]

raoulduke, als je nu een nieuwe kolom aan Object toevoegd zie je die dan ook meteen terug bij File? Dan weten we zeker dat het niet een simpele copy is.

[raoulduke]

Zyphrax, kijk nog eens naar mijn vorige post, ik doe precies wat jij vraagt:
- ik maak een tabel object
- ik maak een tabel file (inherits object)
- voeg een kolom toe aan object

In de uitvoer zie je dat aan het einde beide tabellen een kolom 'omschrijving' hebben.

[Zyphrax]

Oops, keek ik overheen

[EfBe]

ok, dan is het inderdaad een linked schema feature. Wel netjes

(de data is dan nog wel verspreid in 2 tables dus, dus om mijn 3 typen er even bij te pakken, zit je met type 2) ).

[ Voor 48% gewijzigd door EfBe op 02-03-2004 16:43 ]

[Zyphrax]

Werken Object en File hier dan ook samen met één ID?
Als je een record toevoegt aan de File Table zit hiervan het Object "deel" dan in de Object Table?

Hoe werkt de overerving precies bij Tables? Gaat het verder dan het soort van in sync houden van de kolommen.

[raoulduke]

Nog een kleine toevoeging over de werking van INHERITANCE aan de hand van het toevoegen van records. Ik geef even het volgende voorbeeld:

code:

Welcome to psql 7.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

inh=# create table object(id int, naam char(32));
CREATE TABLE
inh=# create table file(pad char(80)) inherits(object);
CREATE TABLE
inh=# alter table object add omschrijving char(64);
ALTER TABLE
inh=# \d object
          Table "public.object"
    Column    |     Type      | Modifiers
--------------+---------------+-----------
 id           | integer       |
 naam         | character(32) |
 omschrijving | character(64) |

inh=# \d file
           Table "public.file"
    Column    |     Type      | Modifiers
--------------+---------------+-----------
 id           | integer       |
 naam         | character(32) |
 pad          | character(80) |
 omschrijving | character(64) |

inh=# insert into object(id, naam, omschrijving) 
values(1, 'Object1', 'Dit is object nummer 1');
INSERT 250664 1
inh=# insert into file(id, naam, omschrijving, pad) 
values(2, 'File1', 'Dit is file nummer 1 met ID 2', '/usr/raoul/duke');
INSERT 250665 1
inh=# select id, trim(naam), trim(omschrijving) from object;
 id |  btrim  |             btrim
----+---------+-------------------------------
  1 | Object1 | Dit is object nummer 1
  2 | File1   | Dit is file nummer 1 met ID 2
(2 rows)

inh=# select id, trim(naam), trim(omschrijving), trim(pad) from file;
 id | btrim |             btrim             |      btrim
----+-------+-------------------------------+------------------
  2 | File1 | Dit is file nummer 1 met ID 2 | /usr/raoul/duke
(1 row)

inh=#

Het Object deel van File komt dus terug in de Object tabel. Dus het is iets anders dan je zegt, Efbe, de PostgreSQL INHERITANCE is gewoon een 'natuurlijke' oplossing voor het subclass probleem: er zijn geen dubbele tabellen, waarden, kolommen - het werkt gewoon volgens het IS A principe, zonder dat je zeg maar de SQL tabellen zelf moet bedenken.

edit:
Even trim() toegevoegd i.v.m. de layout.

[ Voor 36% gewijzigd door raoulduke op 02-03-2004 16:49 ]

[EfBe]

Ah, dat ziet er perfect uit, moet ik zeggen. De columns van object worden dus eigenlijk gemirrored in file, maar maken daar fysiek niet deel van uit. Wel leuke oplossing. Scheelt weer een join

[ACM]

D'r zit alleen een groot nadeel aan Postgresql's manier:

quote: http://www.postgresql.org/docs/7.4/static/ddl-inherit.html

A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children.

Oftewel, je kan geen FK's leggen naar Object als je wilt dat het een FK naar Object, File, Folder, etc moet zijn
Ik overwoog ook die functionaliteit te gebruiken maar deze limiet is wel erg vervelend imho.

[raoulduke]

ACM, kan je iets meer details (in een concrete situatie) geven over deze beperking? Bijvoorbeeld een aantal statements die de beperking direct aangeven bij het aanmaken van PKs of FKs. Op dit moment zie ik nog niet meteen wat de beperking precies inhoudt in een praktijksituatie. Mijn eerste indruk is dat INHERITANCE een goede oplossing is voor subclass problemen.

[EfBe]

ACM schreef op 02 maart 2004 @ 19:02:
D'r zit alleen een groot nadeel aan Postgresql's manier:

Oftewel, je kan geen FK's leggen naar Object als je wilt dat het een FK naar Object, File, Folder, etc moet zijn
Ik overwoog ook die functionaliteit te gebruiken maar deze limiet is wel erg vervelend imho.

Lijkt me toch logisch? Relaties zijn iets anders dan subclasses. Het lijkt me dat je relaties legt tussen absolute types, dus relaties tussen Object en andere entities en bv file en andere types. Een relatie van type zou niet 1 2 3 moeten worden georven door de child types.

Let wel, relaties zijn iets anders dan is-a definities, en niet echt bekend in de OO wereld. (alleen per object, niet per type!).

De postgress manier lijkt op updatabale views in sqlserver. Nadeel van updatable views is echter dat je maar 1 table kunt updaten.

[raoulduke]

Ik ben nog even wat aan het testen geweest, en hieruit volgt:

code:

inh=# create table objects (id serial primary key, naam varchar(32));
NOTICE:  CREATE TABLE will create implicit sequence 'objects_id_seq' for SERIAL column 'objects.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'objects_pkey' for table 'objects'
CREATE TABLE
inh=# \d objects
                                  Table "public.objects"
 Column |         Type          |                        Modifiers
--------+-----------------------+---------------------------------------------------------
 id     | integer               | not null default nextval('public.objects_id_seq'::text)
 naam   | character varying(32) |
Indexes: objects_pkey primary key btree (id)

inh=# create table users (username varchar(16)) inherits (objects);
CREATE TABLE
inh=# insert into objects(naam) values('Object1');
INSERT 250702 1
inh=# insert into users(naam, username) values('User1','username1');
INSERT 250703 1
inh=# create table userreference(id int references users(id), logincount int);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  UNIQUE constraint matching given keys for referenced table "users" not found
inh=# create table objectreference(id int references objects(id), logincount int);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
inh=#

Dus, op de parent van de INHERITS-tabel is een bruikbare FK id aanwezig, op subclasses van deze parent is deze key niet direct beschikbaar. Objects kan dus met FK aangesproken worden, Users, Files en Folders dus niet. Maar daar is natuurlijk weer een eigen key op aan te brengen - dat kan wel.

Edit: Toevoeging, even een klein voorbeeldje met een extra id op users:

code:

inh=# create table objects (object_id serial primary key, naam varchar(32));
NOTICE:  CREATE TABLE will create implicit sequence 'objects_object_id_seq' for SERIAL column 'objects.object_id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'objects_pkey' for table 'objects'
CREATE TABLE
inh=# create table users (user_id serial primary key, username varchar(16)) inherits (objects);
NOTICE:  CREATE TABLE will create implicit sequence 'users_user_id_seq' for SERIAL column 'users.user_id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'users_pkey' for table 'users'
CREATE TABLE
inh=# insert into objects(naam) values('Object1');
INSERT 250734 1
inh=# insert into users(naam, username) values('User1','username1');
INSERT 250735 1

inh=# select * from objects;
 object_id |  naam
-----------+---------
         1 | Object1
         2 | User1
(2 rows)

inh=# select * from users;
 object_id | naam  | user_id | username
-----------+-------+---------+-----------
         2 | User1 |       1 | username1
(1 row)


inh=# create table objectreference(object_id int references objects(object_id), objectcount int);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
inh=# create table userreference(user_id int references users(user_id), logincount int);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE

In dit voorbeeld heb je dus én een unique PK op objects met object_id en een unique PK op users met user_id (user_id != object_id). Op beide PK-s is gewoon een FK reference te maken, dus ik zie het probleem niet zo?

edit:

Daar gaat de layout, maar mijn punt is duidelijk.

[ Voor 41% gewijzigd door raoulduke op 02-03-2004 19:32 ]

[ACM]

Ik bedoel dan ook dit:

SQL:

acm=# create table parent (pid integer primary key);
CREATE TABLE
acm=# create table child (cid integer) inherits (parent);
CREATE TABLE
acm=# create table refp (pid integer references parent(pid));
CREATE TABLE
acm=# insert into child values (1, 1);
INSERT 5325137 1
acm=# insert into refp values(1);
ERROR:  insert or update on table "refp" violates foreign key constraint "$1"
DETAIL:  Key (pid)=(1) is not present in table "parent".
acm=# select * from parent ;
 pid
-----
   1
(1 row)

EfBe schreef op 02 maart 2004 @ 19:20:
Lijkt me toch logisch? Relaties zijn iets anders dan subclasses. Het lijkt me dat je relaties legt tussen absolute types, dus relaties tussen Object en andere entities en bv file en andere types. Een relatie van type zou niet 1 2 3 moeten worden georven door de child types.

Let wel, relaties zijn iets anders dan is-a definities, en niet echt bekend in de OO wereld. (alleen per object, niet per type!).

De postgress manier lijkt op updatabale views in sqlserver. Nadeel van updatable views is echter dat je maar 1 table kunt updaten.

Wat ik er bijvoorbeeld mee zou simuleren is de relaties tussen abstract classes of interfaces en gewone objecten (dat het je niet boeit wat het nou precies voor 'ding' is, maar dat je zeker wilt weten dat het iig een 'instanceof'-relatie met een zekere klasse heeft).

Bijvoorbeeld:
Server: String Servername, List Ips, List Services

Waarbij Service een abstract class is met een naam en er bijvoorbeeld een "HttpService" en "SqlService" zijn die daadwerkelijk een Service implementeren en of representeren. Bij de Server boeit het je in principe niet welke services er nou precies zijn, je wilt gewoon een lijst van de aanwezige Services hebben
De Service zelf weet wel of ie een HttpService of een SqlService of whatever is.

[EfBe]

@ACM: Dan heb je dus een set relaties op het supertype nodig, en inderdaad kan INHERITS dat niet, raoulduke heeft dat net aangetoond . Je komt dan terecht bij de situatie die ik schetste als optie 3): een type is altijd verdeeld over meerdere tables (behalve het supertype) en je zult dus altijd de supertype table moeten lezen voor het lezen van een subtype, daar daar dus ook nog data van het subtype in staan.