mark m wrote:
> Thanks very much!! It also occurred to me that I could have a Table named
> "case1" and another
> named "case1workhist". The RDBMS wouldn't know they were related but my
> application could be
> set up to know this.
>
> Here is more detail on my current data organization:
>
> Open Cases
> Case 1...
> Case 2...
> field 1
> field 2
> work history
> item 1
> item 2
> worker ID
> duration
> type
> rate
> item 3
> ...
> ...
> item n
> Case 3
> ...
> ...
> Case n
>
> In my app, a certain case is chosen to be displayed. All of the above
> information is displayed in one form
> or another within my app. The user can change any given piece of info or
> add new information. So, I guess
> I could just look for the tables "Casen" and "casenworkhist" to display my
> info. I do however need to go through
> and calculate all the hours for all open cases and other calculations like
> that. I want to be sure I'm setting things
> up in a way that will allow me to do this with minimal overhead.
>
> In your method I would only have 1 workhist table?? whereas in my method I
> would have n workhist tables. Is it
> better to have only 1 and use a foriegn key like you describe to link work
> history records with a given case?? Is the
> rule generally to minimize the number of tables??
A rule for relational database best practices is to minimize the number of
tables that are mutually homogeneous in meaning and structure, and to allow or
exploit multiple tables that are mutually heterogeneous. If you have a
conceptual reason for having multiple same-looking tables, then you encode that
as an extra column in the 1 table.
So for example, the work history details for *all* of your cases would go in
*1*
work_history table, not a separate table for each case, and you would have a
field in work_history called case_number to identify which records of that
table
belong to each case.
Given the hierarchy you mention, a table layout like this might work:
CREATE TABLE cases (
case_number INTEGER PRIMARY KEY,
<field 1>,
<field 2>
)
CREATE TABLE workers (
worker_id INTEGER PRIMARY KEY,
...
)
CREATE TABLE work_histories (
work_history_item_number INTEGER PRIMARY KEY,
case_number INTEGER,
worker_id INTEGER,
duration,
type,
rate,
UNIQUE KEY (case_number, worker_id) # or make this pk instead
FOREIGN KEY (case_number) REFERENCES cases (case_number),
FOREIGN KEY (worker_id) REFERENCES workers (worker_id)
)
By the way, I name my tables in plural to describe what the table as a whole
represents, (much like how one might name an array variable in a program, which
is what a table is analogous to save for not being ordered), which is for
example a collection of cases. But some people will tell you tables should be
named after what an individual record in it represents; I disagree with them
(it
is like naming an array after what one of its elements is) but it is commonly
practiced, and its a style issue in the end, the DBMS doesn't care.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
