• Questions on keys and key migration

    From marc@21:1/5 to All on Sat Sep 9 19:18:45 2023
    I have studied IDEF1X for some time, which has greatly
    improved my database designs. I have read previous
    discussions in this newgroups and browsed online through the
    many excellent documents by SoftwareGems.

    I think I understand IDEF1X methodology quite well, bit
    I still have a few doubts:

    1. under what circumstances is it possible, necessary or
    advisable to migrate an alternate key rather than the
    primary key? For example, in this document:


    the AK (Student,DateTime) is migrated from Exam Booking to
    Exam Location. Is that a bad design practice? I am not
    talking about this specific example, but about a general
    approach. AFAIK, IDEF1X does not allow migrating anything
    other than primary keys, but maybe that's too restrictive?

    2. under what circumstances is possible, necessary or
    advisable to enforce a unique constraint (AK) on a subset of
    a primary key? This is something I have seen in some
    documents by SoftwareGems, see for example CustomerCar and
    DealerCar here:


    or FormField here:


    That seems to contradict what I have learned, namely that
    a primary key must be a *minimal* set of unique attributes.
    AFAIU, that is done to strenghten integrity. But then, why
    not define a minimal primary key and migrate the key plus
    some other attributes? If implemented, this solution would
    require one index (on the primary key). But if a subset of
    the key is subject to a unique constraint, then one needs to
    define two indexes. For instance, in the above model there
    must be an index on (FormName,FieldName,FieldType) and
    another one on (FormName,FieldName). It seems to me that the
    latter makes the former unnecessary (maybe in some DBMS, one
    cannot define a foreign key constraint on a set of
    attributes larger than a key?)

    3. in Loomis, The Database Book (which I found on
    archive.org), I have seen an example (fig. 3.23) of
    a category hieararchy in which the primary key of the parent
    migrates outside the primary key of some category (I know
    that subtype is better, maybe I should say subtype). The
    generic entity is:

    Investment(Date, Source)

    with PK (Date,Source). That is specialized into:

    Stock(Company, Grade, Date, Source), with PK (Company, Grade, Date)

    (yes, that is not a mistake) and

    Bond(Bond#, Date, Source) with PK (Bond#)

    The book describes Data Modeling Technique (DMT), not
    IDEF1X, so the rules may not be the same. It is clear to me
    that Stock cannot be a subtype of Investment in IDEF1X
    because it's one-to-many. But (and this is partly related to
    my first question) the example left me wondering whether
    Bond can be considered a subtype if (Date,Source) is made an
    alternate key of Bond. Is this (subtyping and then
    exchanging the PK with an AK) bad practice?

    I thank in advance whoever would find the time to help me
    resolve some of doubts. I would appreciate it if any
    guideline you may provide is accompanied with the reasonin
    behind it, rather than just say "that's fine/that's bad".


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)