Good modelling practice?

From: David Hodgman <hodgmand_at_ozemail.com.au>
Date: 1996/02/28
Message-ID: <31353ABC.3C7E_at_ozemail.com.au>#1/1

Hello all,

A few areas of data modelling theory that I'd really appreciate some discussion on:

1/

I have a table ENTITLEMENT_TYPE which appears as follows:

entitlement_type_no (PK)
entitlement_category_code (AK1)
entitlement_type_code (AK1)
entitlement_sub_type_code (AK1)

standard_units_code
warning_level
etc...

Properly broken down this would be modelled as a three table hierarchy:

(a) entitlement_category_code (PK)

(b) entitlement_category_code (PK) (FK)

     entitlement_type_code (PK)

(c) entitlement_category_code (PK) (FK)

     entitlement_type_code (PK) (FK)
     entitlement_sub_type_code (PK)
     standard_units_code
     warning_level
     etc...

However, when discussing functional dependencies, the associative attributes such
as warning_level sometimes apply to all entitement_types within a category and in other cases
vary at the sub_type level within a category. In the first case, it would be a pain for the user
to go through all of the sub-types if the warning_level for a whole category has changed and yet
I need the flexibility this provides. Any comments?

2/ Existence dependency.

I have a strong grasp of the concepts of existence and identification dependencies, however on
the practical side I am puzzled as to what the best practices are. Existence dependency is a nobrainer
 but whether to donate it to the primary key of the child entity or just have it as a Not
Null foreign key is a question I would like some guidelines on.

3/ Identification dependency

Similarly, in a case such as Order and Order_Item, are there any guidelines about the structure
of the childs primary key in this case. If the Order_Item.id is an arbitrary system key that is
meaningless and hidden from the user, what matter if it is unique within the Order only or
unique across the whole table. My inclination is to carry the Order.id as a Not Null foreign key
and have a simple primary key. This sort of practice can really reduce the size of primary keys
when you get several levels deep.

4/ On the other hand....

I have been to a site where the data modeller took current theories to the max and implemented a
hidden system level primary key on EVERY table and set up the natural primary keys as unique
alternate keys. I could see his point in the debate regarding the abstraction of the data from
the physical database but, jeez, it confused the crap out of a lot of the developers and I
imagine it will make maintenance more rather than less of an ongoing headache.

Discussions on any of these areas eagerly looked forward to.

Regards,

Dave Hodgman
Certified Powerbuilder Developer - Professional Enigma Software Consultants Received on Wed Feb 28 1996 - 00:00:00 CET