From: Sam V. <sa...@vi...> - 2004-11-30 05:23:31
|
Dave Rolsky wrote: > On Tue, 30 Nov 2004, Sam Vilain wrote: > >> http://www.class-dbi.com/cgi-bin/wiki/index.cgi?ERD > > > It'd be nice if the ERD indicated PKs for each table. It'd also be good > to explain the relationship between artist, person, & band, since it's > not obvious. There is an implied constraint that a given artistid must > appear in either the band or person table, but not both, I think. > That's pretty crucial to making this particular schema make sense. That portion of the diagram is supposed to represent inheritance, as I understand it. A band IS an artist. A person IS an artist. A band IS COMPOSED OF artists. so, the band / person / artist tables all use the same ID values. This is all unambiguous if represented in UML; I will put up a UML diagram on that wiki (if possible, if not elsewhere) on the morrow. >> 1. re-org the database (clean up IDs and/or garbage collect) - may >> load entire database into core and write result to a second >> database if required. May make arbitrary decisions about what >> constitutes the sane starting points, for example leaving behind >> artists with no CDs. > I'm not sure I understand what this is about. Is this some sort of > script to check referential integrity? Why not just enforce > referential integrity in the DBMS? That's what it's there for. > If you have to write a separate script for that you're already up a creek. If you prefer, you can interpret this question as an open question into how you would go about managing some of the longer-term management issues that arise with databases. One of those issues is recycling IDs. The time honoured approach to this (with the side effect of compacting tables) is to simply relink all the objects in the entire database; the "re-org". In fact AIUI this is why one of the rules of normalisation keeps database IDs and user data seperate. Maybe it will emerge that the re-org was never really required after the advent of the RDBMS model and referential integrity constraints. However not all entries will be using their RDBMS to this level. -- Sam Vilain, sam /\T vilain |><>T net, PGP key ID: 0x05B52F13 (include my PGP key ID in personal replies to avoid spam filtering) |