[Database] Relationeel probleem, advies graag!

[Cerntje]

Ik heb geen programmeertechnische, maar meer een databasetechnische vraag. Ik ben voor me stageopdracht bezig om een webapplicatie met database te ontwerpen. Het gaat om het beheren van apparatuur. De gegevens voor deze apparatuur stond voorheen in een Oracle 8i database. De gegevens daar bestonden bijv. uit naam, barcode, serienr. etc. Deze gegevens zijn inmiddels al overgezet naar een MySQL database.

Vanuit de gebruikerskant is de vraag om de technische specificaties van de apparatuur aan de database toe te voegen, dit om via een zoekmogelijkheid de juiste apparatuur te kunnen zoeken. Deze gegevens zijn niet aanwezig in de Oracle database.

De bedoeling is dat alle technische specificaties en extra informatie per apparaat via de webapplicatie ingevuld gaan worden en zo opgeslagen wordt. Ik stuit hierbij op een (voor mij iig) complex databaseprobleem.

code:

Database: 

Equipment ---> EquipmentCategory 
        |
        | ---> Equipmentspecs
            |
            |---> FrequencyOptions 
            |
            |---> TimeOptions
            |
            |---> MathOptions

In de tabel equipment staan alle standaard gegevens welke overgezet zijn vanuit de oracledatabase. Elk apparaat wordt gekenmerkt door een unieke barcode en een typenummer waaraan zijn categorie en type hangen. (vb. Apparaat met barcode 12345 en typenummer 1 is een machine van het type boor aka. boormachine)
In de tabel equipmentCategory staan een typeID met category en typeaanduiding
De tabel EquipmentSpecs staat een overzicht van de relaties met de technische specificaties.

Vanuit het normaliseren ben ik terecht gekomen bij de relatietabel. Alle mogelijke waarden van technische specificaties staan dus in de specificatiestabellen met hun ID. In de EquipmentSpecs tabel komen deze samen met alleen het type of direct aan het apparaat zelf met de barcode.

code:

EquipmentSpecs:

--------------------------------------------------------------------
| Typenr. | Barcode | FrequencyOptions | TimeOptions | MathOptions |
--------------------------------------------------------------------
|    1    |         |        1         |      2      |     3       |
|    1  |         |        2         |             |     4       |    
|    1  |  12345  |        1         |      2      |     4       |
|    2   |       |        3         |      1      |             |
--------------------------------------------------------------------

Daaronderliggende tabellen staan de specificaties in.
Voor elk typenummer (lees soort apparaat) moeten de mogelijke technische specificaties bekend zijn. (Bijv. bereik van een voltmeter; 0-100 volt, 100-200 volt, 200-500volt.

code:

-------------------------------------
| ID | FreqRangeLow | FreqRangeHigh |
-------------------------------------
| 1  |      0       |       100     |
| 2  |      100     |       200     |
| 3  |      200     |       500     |
| 4  |      500     |       1000    |
-------------------------------------

Uit deze tabellen is af te leiden dat apparaten met het typenummer 1 de mogelijke waarden 0-100 en 100-200 kunnen hebben. Apparaat met barcode 12345 is dus bijv. een voltmeter met bereik 0-100.

Het probleem is nu dat als nieuwe apparaat gevonden is in de MySQL database deze getoond wordt in de webapplicatie. Als eerste moet er een category en type gekozen worden welke tot het apparaat behoren, dit stuk is al klaar. Zodra deze 2 gekozen zijn moet er een overzicht gegenereerd worden met de 'mogelijke' technische specificaties van de gekozen category/type. De gebruiker kiest de waarden voor het specifieke apparaat en slaat deze op in de database.

Mijn vraag is nu, heeft iemand een soortgelijke ervaring met het opzetten van zulke relaties? Of heeft iemand adviezen/suggesties waar ik op moet letten? Het klinkt wel redelijk makkelijk zo, maar de mogelijkheid die ik nu beschreven heb is aardig moeilijk te realiseren in de webapplicatie, omdat er straks ongeveer 300 verschillende typenummers zijn met al hun mogelijke technische specificaties.

Screenshot deel ERD:

[whoami]

Een eerste vraagje, waarom ga je van Oracle naar MySQL ?

[Haploid]

In plaats van die koppel-tabel "Equipmentspecs", kun je ook de primary key van de verschillende specs-tabellen laten verwijzen naar een type en/of barcode. Dus met "Typenr" als de primary key (en foreign key verwijzend naar de "Type" tabel) EN met "Typenr, Barcode" als een unique key (en foreign key verwijzend naar de "Equipment" tabel).

Dus iets als:

FrequencyOptions
-----------------------
TypeNr$
Barcode
Value
UNIQUE KEY (Typenr, Barcode)
FOREIGN KEY (TypeNr) REFERENCES Type(ID)
FOREIGN KEY (Typenr, Barcode) REFERENCES Equipment(Typenr, Barcode)

Waarbij Barcode eventueel NULL mag zijn. Zo heb je geen koppeltabel nodig, maar kun je wel al die info kwijt.

[ Voor 20% gewijzigd door Haploid op 29-11-2004 10:05 ]

[curry684]

whoami schreef op maandag 29 november 2004 @ 10:00:
Een eerste vraagje, waarom ga je van Oracle naar MySQL ?

Ahum inderdaad, ik zag die opmerking, zag de bijbehorende data en vroeg me ook al af waar het heen ging met deze wereld

@ Haploid: waarom definieer je zo netjes foreign keys in een database die ze toch negeert?

[Bosmonster]

curry684 schreef op maandag 29 november 2004 @ 10:33:
[...]

Ahum inderdaad, ik zag die opmerking, zag de bijbehorende data en vroeg me ook al af waar het heen ging met deze wereld

@ Haploid: waarom definieer je zo netjes foreign keys in een database die ze toch negeert?

forward compatibility? v5 gaat het ondersteunen...

[ACM]

curry684 schreef op maandag 29 november 2004 @ 10:33:
@ Haploid: waarom definieer je zo netjes foreign keys in een database die ze toch negeert?

Als je gebruik maakt van innodb-tabellen, dan worden ze niet genegeerd hoor. Echt perfect uitgevoerd ook niet, magoed.

[Verwijderd]

Als je gebruik maakt van het tabletype InnoDB ondersteunt mysql volgens mij wel FK?? Daarnaast is het voor jezelf wel prettig om een realistisch schema neer te zetten hoe je db in elkaar steekt...

[edit]

net te laat

[ Voor 8% gewijzigd door Verwijderd op 29-11-2004 10:40 ]

[curry684]

ACM schreef op maandag 29 november 2004 @ 10:39:
[...]

Als je gebruik maakt van innodb-tabellen, dan worden ze niet genegeerd hoor. Echt perfect uitgevoerd ook niet, magoed.

Maakt ook niet uit, het gaat zo te zien alleen maar om redelijk kritieke data voor het bedrijf, dus data integriteit is vast niet zo belangrijk. Waarschijnlijk net zo belangrijk als transactional integrity, durability en andere vormen van consistency, als men van Oracle naar MySQL overstapt

[Haploid]

Bosmonster schreef op maandag 29 november 2004 @ 10:37:
[...]


forward compatibility? v5 gaat het ondersteunen...

Hehe, laten we het op forward compatibility houden. Maar OMG, MySQL heeft geen foreign keys?! Dat lijkt me toch een van de hoekstenen van een DBMS. Wat worden we toch verwend met Oracle.

[Bosmonster]

Haploid schreef op maandag 29 november 2004 @ 10:51:
[...]


Hehe, laten we het op forward compatibility houden. Maar OMG, MySQL heeft geen foreign keys?! Dat lijkt me toch een van de hoekstenen van een DBMS. Wat worden we toch verwend met Oracle.

Foreign keys zijn ook redelijk eenvoudig op software-niveau te regelen. Aangezien MySQL bedoeld is (of ooit was) als eenvoudig en snel DBMS had dat dus geen hoge prioriteit.

[Verwijderd]

Daarnaast kan je gewoon gebruik maken van transactions zodat je op die manier integriteit kan bewaren en verder kan je zelf netjes cascade query's bouwen zodat delete's e.d. prima te controleren zijn. Valt dus wel mee met mysql vind ik persoonlijk...

maar goed, dit gaat best off-topic

[curry684]

Verwijderd schreef op maandag 29 november 2004 @ 11:11:
Daarnaast kan je gewoon gebruik maken van transactions zodat je op die manier integriteit kan bewaren en verder kan je zelf netjes cascade query's bouwen zodat delete's e.d. prima te controleren zijn. Valt dus wel mee met mysql vind ik persoonlijk...

Sinds wanneer heeft MySQL ondersteuning voor transactions, cascading deletes en on-delete triggers?

[ACM]

Als je uitsluitend met InnoDB (dus al sinds mysql 3.x) tabellen werkt heb je ondersteuning voor transactions, foreign keys en ik meen sinds redelijk kort ook cascades daarbij. Triggers verder nog niet.

[Verwijderd]

met name vanaf 4.0 en 4.1 zijn er op dat gebied al zeker verbeteringen aangebracht en vind ik dat een mysql db, mits goed toegepast perfect te gebruiken is en data integriteit prima bewaart kan worden.

http://dev.mysql.com/doc/mysql/en/COMMIT.html
http://dev.mysql.com/doc/mysql/en/DELETE.html

ondelete triggers (oftewel op basis van FK) weet ik niet zeker of die bestaan in stable releases.

[edit 1]

doet ACM het weer



[edit 2]

ook automatische deletes en updates zijn dus mogelijk (de vraag is natuurlijk wel in hoeverre je dat altijd wil, maar goed das een hele andere discussie en gaat nog offtopicer dan het nu al is)

http://dev.mysql.com/doc/...eign_key_constraints.html

[ Voor 31% gewijzigd door Verwijderd op 29-11-2004 11:31 ]

[Cerntje]

Alvast bedankt voor de vele reacties

curry684 schreef op maandag 29 november 2004 @ 10:33:
[...]

Ahum inderdaad, ik zag die opmerking, zag de bijbehorende data en vroeg me ook al af waar het heen ging met deze wereld

@ Haploid: waarom definieer je zo netjes foreign keys in een database die ze toch negeert?

De keus voor MySQL was snel gemaakt. Voor mijn stageopdracht was namelijk al een prototype van de webapplicatie gemaakt in PHP/MySQL. Toen ik begon was dit dan ook een eis vanuit het bedrijf. Ik heb naderhand voordat ik aan het 'uitleesscript' begon de mogelijkheden bekeken om toch Oracle te gebruiken en alles om te zetten maar dit bleek uiteindelijk niet haalbaar.

Ik werk hier met PHPMyadmin 2.5.5 en MySQL 3.23.41. Aangezien het een groot bedrijf betreft en veranderingen hier erg lang over doen gerealiseerd te worden moet ik het hier mee doen. Ik heb al gemerkt dat relationeel betreft deze combinatie niet gigantisch is, de SQL-statements in de webapplicatie moeten dan ook zo gedefineerd worden dmv. joins om de correcte informatie boven te krijgen.

[Verwijderd]

Dat je joins moet gebruiken is de normaalste zaak van de wereld, dat heeft niks te maken met het DBMS wat je gebruikt.

Een upgrade naar een recentere versie van mysql zou geen kwaad kunnen en lijkt me wel het overwegen waard en is daarnaast goed te onderbouwen op het gebied van data integriteit en mogelijkheden op SQL gebied.

[Cerntje]

Verwijderd schreef op maandag 29 november 2004 @ 12:29:
Dat je joins moet gebruiken is de normaalste zaak van de wereld, dat heeft niks te maken met het DBMS wat je gebruikt.

Een upgrade naar een recentere versie van mysql zou geen kwaad kunnen en lijkt me wel het overwegen waard en is daarnaast goed te onderbouwen op het gebied van data integriteit en mogelijkheden op SQL gebied.

Een dergelijke upgrade is aan te vragen, maar dat zal via een officiele route moeten welke zeker een aantal maanden gaat duren... en tegen die tijd zit ik hier allang niet meer. Voor mij persoonlijk zal dat dus in ieder geval geen zin hebben.

Wat betreft de inhoud van dit topic betreft.... niemand ziet ernstige fouten of iets dergelijks? Ik ben namelijk benieuwd of ik het met deze opzet helemaal goed kan uitwerken in 1 keer.

[Verwijderd]

Als jij aangeeft dat het essentieel is voor het uitvoeren en opzetten van een goed en betrouwbaarsysteem zal zoiets wel eerder te regelen zijn toch? Het is maar net hoeveel druk je weet uit te oefenen.

Je hebt in je ERD practisch allemaal 1:1 relaties staan, is dat daadwerkelijk de bedoeling?

[D4Skunk]

Wat je hier aanduidt is een algemeen probleem in alle relationele databases (dus ook Mysql & oracle) : het gebruik van subtypes....

Ik heb een klein voorbeeldje gemaakt om aan te tonen wat volgens mij de beste oplossing is :

code:

4 tabellen : 
Object_base(varchar  uid,varchar viewname) 
       primary key(uid)
Persons_base(varchar  uid,varchar  name) 
       primary key(uid)
       foreign key(uid) references Objects(uid)
Employees_base(varchar  uid,numeric wage) 
       primary key(uid)
       foreign key(uid) references Persons(uid)
PayChecks_base(varchar uid,varchar Employee_id,DateTime date)
       primary key(uid)
       foreign key(uid) references Objects(uid)

4 updateable views :
Objects(Object_base.uid)
Persons(Objects.*,Persons_base.name)
Employees(Persons.*,Employees_base.wage)
PayChecks(Objects.*,PayChecks_base.Employee_id,PayChecks_base.date)
       foreign key(Employee_id) references Employees(uid)

Je hebt dus als het ware een extra niveau van abstractie nodig op je relationale database.
Je moet er voor zorgen dat al je '*_base'-objecten niet rechtstreeks toegankelijk zijn, enkel via de view.
1 groot nadeel : de inserts/deletes zijn niet zo eenvoudig aan te sturen (trigger-level misschien)

>> EDIT

Gebruik cascaded deletes on 'objects_base', en 'cascaded inserts' simulatie dmv trigger voor inserts, en alle problemen zijn opgelost

[ Voor 13% gewijzigd door D4Skunk op 29-11-2004 15:57 ]

[drm]

curry684:
Sinds wanneer heeft MySQL ondersteuning voor transactions, cascading deletes en on-delete triggers?

MySQL niet, maar PHP wel