Re: Lots of code. How many tables.

Ben,

It is hard to talk directly about the examples you pose, because too many questions come to mind in terms of exact requirements.

Elmasri and Navathe, in their book, _Fundamentals of Database Systems, 3rd. Ed._, give a nice overview of how to model these types of problems involving class/subclass specialization, generalization, lattices, and hierarchies; and they provide a somewhat formal algorithm or framework on choosing a relational implementation to map to that enforces structure, behavior, and integrity at the dbms level. There are a few options, and based on the requirements and trade-offs (and inherent limitations of any given SQL implementation), the relational representation can differ, but still for the most part, fully support the constraints/predicates presented in the semantic extended model, whether they be mutually disjoint classifications, overlapping, multiple inheritance, union types, or multi-valued classifications.

Some approaches towards mapping to a relational model are better than others; some options perhaps provide better performance, but at the expense of introducing more complexity (e.g. nulls), and should probably be avoided, if possible.

See chapters 4 and 9.

Regards,

Dan

"ben brugman" <ben_at_niethier.nl> wrote in message news:402ce522$0$1408$4d4ebb8e_at_read.news.nl.uu.net...
> > > Only as a last resort when one has no other option. Otherwise, it only
> makes
> > > sense to manage data integrity with a data management system.
> >
> > Well put! Apart from being blinkered ("the only way data gets in is
> > via THIS application code"), enforcing data constraints in the
> > application is far more difficult than most people who try to do it
> > realise. For a start, EVERY bit of application code that inserts a
> > row into a table that has an application-enforced "foreign key" to a
> > generic code table (GCT) better LOCK the parent row in the GCT to
> > prevent another user deleting it...
>
> Going with the two of you, how to implement a constraints with
> subtypes. For example we have a supertype A which can have
> 5 subtypes B,C,D,E,F.
>
> There is a type X which has to be of subtype C or D.
>
> And there is a type Y which can be of subtype C,D,E, (one, two or
> three at the same time).
>
> As fare as I know the above can only be implemented in code.
> (trigger or application and as we know not all databases have
> the same syntax or features in triggers).
> If the database has not been choosen and the application is
> (Java for example) than this constraint can be simpler implemented
> in Java than in a database which is not specified yet.
> (If you have another solution to this 'arc' problem which is simpler,
> please inform me).
>
> Or a more practical example.
> members can lend books, but only to an amount that can differentiate
> for each member. Most members can only lend 5 books, but some members
> can lend more, some less.
> (The risc of somebody being allowed to lend too many books is acceptable,
> the cost to prevent this should remain low).
> Implementation in triggers is not an option because the choosen RDBMS does
> not support triggers.)
>
> I can build a model were this is checked by relational constraints.
> But I can also build this in the application, this last is simpler to
> implement and
> therefore cheaper.
> Here the criteria are met by implementing the constraint in the
application.
> This is cheaper to build than to make a (new) model which does implement
> this constraint. So under economical rules this is the better solution.
>
> For me, the above situations, supply arguments that application code
> (business logic) can implement constraints. In most practical situations
> the application does do a lot of constraint checking.
> Constraints which can be implemented as relational constraint in
> the database SHOULD be implemented in the database. But relational
> constraints are not the only constraints.
>
> ben
>
>
Received on Fri Feb 13 2004 - 15:57:07 CST