Re: 1:c - relation in oracle

"Christian Nein" <christian_at_nein.de> wrote

> I think you got me wrong. Here is an example of what I mean:
>
> Let's say we have 2 tables, one containg INVOICES and a second one containg
> the associated INVOICE LINE ITEMS
>
> case 1)
> A relation shall ensure that each INVOICE exactly has 2 INVOICE LINE ITEMS
> (not 0 or 1 or 3 or 4 or ...)
>
> case 2)
> A relation shall ensure that each INVOICE has maximal one (0 or 1) INVOICE
> LINE ITEMS
Good designs scale. Bad designs does not.

Let me throw an example at you.

You have a PERSONS table. You need to carry the PARENT relationship. This is what you would call your 1:2 relationship.

The simplest way is to do this:
PERSON=(person_key, blah blah..., father_person_key, mother_person_key)

It is also the sure way of not creating a scaleable design.

The correct way of doing this is to create a PERSON_RELATIONSHIP entity.

PERSON_RELATIONSHIP=(person_key1, person_key2, relationship_type)

This now caters for not only parent relationships, but for step-parents, same-sex parents and so on. This design can also scale for *any* future requirement ito person relationships.

Back to your invoice example.

You can do this:
INVOICE_HEADER=(invoice_key, blah, blah, line1_key, line2_key )

But why?

Designing a database for rules such as "an invoice may have only 2 lines" is stupid. The design should be flexible to cater for the future where this rule changes into "an invoice may only have a 100 lines". Use triggers to enforce these type of rules - do not cut the legs off the db design as a result.

One of the biggest problems I've seen time and again in production systems are applications, SQLs and db designs that do not scale. It may work fine for the a year or so, but when data volumes grow (as the business grows), the crap starts to hit the fan.

--
Billy