[Shacs] database structure question.. this one is easy

[Fuermann, Jason]

I did like Dr. Hartness's frequency/probability field.

From: shacs-bounces at shsu.edu [mailto:shacs-bounces at shsu.edu] On Behalf Of Shelledy, Brian
Sent: Wednesday, February 25, 2009 11:48 AM
To: 'Bronius Motekaitis'; James.D.Wyman at gmail.com; shacs
Subject: RE: [Shacs] database structure question.. this one is easy

Regardless of the internal or external table, if you wanted to do more then one default for each court then you'd at least have to make a compound key as courtid would be the same for each default.  I still agree with the external table though, as there is less duplication that way if you choose to use multiple defaults.   Maybe you could use a location rank field to rank the options or something.

From: shacs-bounces at shsu.edu [mailto:shacs-bounces at shsu.edu] On Behalf Of Bronius Motekaitis
Sent: Wednesday, February 25, 2009 9:26 AM
To: James.D.Wyman at gmail.com; shacs
Subject: Re: [Shacs] database structure question.. this one is easy

Thanks all, for your answers thus far.  Be sure to do a ReplyAll or change the Bronius to a SHACS so the world can bask in your intelligence.

So far, I have 2 for a defaults table, 1 opposed.  I'm leaning toward the defaults table, too: in addition to making it clear that these are "default" values without renaming foreign keys, it would also, should I so desire some time in the future, the developer to allow multiple defaults per court.  Say a given Court's Hearing is always held in one of two Locations 50% of the time.  Putting defaults on the Court table would disallow for this expansion without making a "dummy court entry" to reflect each of either case.  That would be bad design..

To clarify one response: no, the 2nd example doesn't have the location, judge, required.. in it, because those are just default/seed values.  The whole point of the defaults table was that in creating a Hearing, pull a few Hearing value defaults from somewhere-- these aren't defaults for the Court table when creating a new Court.

* note: replace "Hearing" with "Trial" for my diagrams.. that was a recent change in schema to better reflect the real world :)

About one fellow's comment on trial duration: yes, good point to consider.  However, for the scope of this project (it's an online prequalification/impaneling system for jurors), trial duration can be 1 day on through 7 or 14 days long.  1hr and 1/2 day sessions get rounded up.

-bronius
On Wed, Feb 25, 2009 at 8:58 AM, <James.D.Wyman at gmail.com<mailto:James.D.Wyman at gmail.com>> wrote:
So you want to default values in a database? Why not create a trigger if these values are empty to insert a default value? I do not reccomend auto populating anything, instead have the input program generate the default and have a hardcoded default or a table you reference for default values for the program that are kept seperate from the data being stored. You should never have database driven programs that populate user data inside of themselves. I would say use two different sets of tables. One table for information to drive the program and pre-populate fields and your other database to store the information.


On Feb 25, 2009 8:53am, Bronius Motekaitis <bronius.motekaitis at gmail.com<mailto:bronius.motekaitis at gmail.com>> wrote:
> Let's consider the tables: Hearing, Court, Judge, and Location (notice the CAPS: I did that so I wouldn't scare away any Access fans and undergrads..!)
>
> A Hearing can hear any Court's cases at any Location by any Judge, so these are all foreign keys on Hearing. When creating a Hearing, I want to populate default values for Judge, Location and a handful of other values not shown here based on the Court selected.
>
>
> Where should I attach these default values?  Does it make more sense to make a "Court_Default" table or stuff the given Court table with these foreign key values as defaults?
>
> See attached for comparison of the two models considered..
>
>
> -bronius
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 11447 bytes
Desc: not available
Url : http://lists.shsu.edu/pipermail/shacs/attachments/20090225/7092ace2/winmail.bin