|
In my mind I always pick a single surrogate key for all tables. I--
stay away from composite keys at all cost.
It is an extra column but it gives you these advantages:
1. All child tables foreign keys only need a single column to
reference the primary key.
2. Very easy to update the natural keys in table without needing to
update every child table with foreign keys 3. Smaller primary/foreign
key indexes. This can make the database run faster, for example when a
record is deleted in a parent table, the child tables need to be
searched to make sure this will not create orphans.
Narrow indexes are faster to scan.
4. ORM's (object relational mappers) always work much more beautifully
with surrogate keys.
5. Natural Keys especially composite keys make writing code a pain.
When you need to join 4 tables the "where clause" will be much longer
(and easier to mess up) than when single surrogate keys were used.
6. Query performance is improved as the database is able to search
attributes to locate the surrogate key and join all child tables by a
single numeric key (sort of related to #5 and #3)
Only downside is the following:
1. you will have more indexes because you most likely will also want
to index whatever natural (aka composite) keys that exist in the data.
Now consider you want to design "Meals" whereby you want to take a
selection of protein to starch and protein to vegetable mappings.
Using the mapping tables as I defined them is much better way to go
because you can just reference the single surrogate keys in those
mapping tables. It would be a nightmare to have to reference those composite keys.
Matt
-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Thursday, December 11, 2014 8:58 AM
To: Midrange Systems Technical Discussion
Subject: Re: Database design issue: seems really bad, yet I'm having
trouble coming up with anything better
James,
It sounds like a standard associative entity (ie. junction or map) to
me used to store a many to many relationship.
https://en.wikipedia.org/wiki/Many-to-many_(data_model)
There usually isn't much too them compared to the master tables whose
relationship they store.
There really isn't a better way to handle them in the DB. I'd used
Matt's model, except that there wouldn't be a ProteinStarchMapID or
ProteinVegtableMapID. I'd use a composite PK made up of the two FK to
the corresponding two master tables.
ProteinStarchMap File:
ProteinID, StarchID
ProteinVegtableMap File:
ProteinID, VegtableID
Personally, I'd store it sparsely. The existence of a record means
the starch/vegetable is compatible. Otherwise it is not.
Alternatively you could add a compatible flag and store every possible combination.
From a maintenance standpoint, it's easy select a given protein and
generate a list of all possible starches (from the Starch Master) with
a flag showing rather or not a record exists in ProteinStarchMap. The
uses just goes through and checks or unchecks the boxes.
Assuming you have need of a "Weekly Menu" that stores the schedule of
selected meals. The Menu (or MenuMap) table would simply be
ScheduledDate ProteinID StarchID VegtableID
Using the associative tables, it's real easy to make sure only valid
meals are stored by defining a FK constraint...
foriegn key (proteinID, starchID) references ProteinStarchMap
(proteinID,
starchID)
foriegn key (proteinID, vegtableID) references ProteinVegtableMap
(proteinID, vegtableID)
Lastly, have you ever looked at Object Role Modeling?
http://www.ormfoundation.org/
I've found it useful for DB design & validation. Particularly when
interacting with the non-technical.
On Wed, Dec 10, 2014 at 9:09 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:
On 12/10/14 5:27 PM, Evan Harris wrote:8.
A mapping file seems the logical way to go about it, and it seems
like it would naturally lend itself to some kind of caching
approach, which might just overcome your bottleneck concerns.
The problem with the mapping file approach is, as I said, the
overhead if it's done within the application's framework: its record
structures have, before a single user-defined field is added:
Fields for creation date, creation time, and creation user Fields
for last-update date, time, user, job, and program An automatically
generated ID field A flag field.
That totals 86 bytes of overhead, per record, in addition to
whatever overhead is inherent to DB2/400.
The "protein" code is 3 characters. The "vegetable" or "starch" code
is
for the desired "protein."
86 bytes of overhead for 11 bytes of mapping data, in a file that
would have to have a separate record for each valid combination of
"protein" and "vegetable," and the same, in another file, for each
valid combination of "protein" and "starch." Something about that
strikes me as being not much of an improvement over the initial
hierarchical solution that seemed so wrong.
Putting space for up to 10 "protein" codes, space-delimited, in each
"starch" and "vegetable" record, would add 39 to 41 characters
(depending on whether we need leading and/or trailing spaces for the
entire field) to each "starch" and "vegetable" record. With 20
"protein" codes per record, it would be 79-81 characters. Then, the
search would work out to nothing more than, say, looking for all
"starch" records for which the "protein list" field contains the
code
--
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
