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??
Mark
On Fri, Oct 30, 2009 at 2:44 AM, Darren Duncan <dar...@darrenduncan.net>wrote:
> mark m wrote:
> > I'm very new to database programming so this question is pretty basic....
> >
> > I have data that is currently organized as follows:
> >
> > Each case has several fields that contain only one value. There are
> several
> > fields that have a pipe-delimited string
> > that represents a work history. Each work history item has its own
> fields
> > like data, worker ID etc. So, as I convert
> > my text files over to database format, I find myself wanting to have a
> table
> > within a table. So, ideally the case table would
> > have several singular fields and a field named "work history" that would
> > contain a table that would have all of the work history
> > in it.
> >
> > But, I haven't found a way to do this. If this is not possible, what
> would
> > be the best way to organize data such as this in
> > a database program.
> >
> > Thanks for the help.
> >
> > Mark
>
> What you are talking about is perfectly reasonable from a logical
> standpoint,
> and in the relational model the feature would be called "relation-valued
> attributes" or "RVAs". Or at least it is in the version of the relational
> model
> that allows non-scalar attribute values, but that is the one that Chris
> Date et
> al, as well as myself ascribe to. Logically speaking, RVAs are what you
> get as
> the intermediate stage of a "GROUP BY", and are the input for aggregate
> operators like SUM()/COUNT()/MIN()/MAX()/etc.
>
> However, SQLite and many other SQL DBMSs don't support RVAs, so you'll have
> to
> use a logically equivalent arrangement of 2 sibling tables that have a
> parent/child (say, "worker"/"work_history") foreign-key relationship.
>
> That is, instead of this kind of schema (in pseudocode):
>
> var worker : TABLE {
> worker_id : INT, b : TEXT, work_history : TABLE { c : INT, d : TEXT }
> }
>
> ... you have this kind of schema:
>
> var worker : TABLE { worker_id : INT, b : TEXT }
> var work_history : TABLE { worker_id : INT, c : INT, d : TEXT }
>
> ... and work_history.worker_id has a foreign key constraint on
> worker.worker_id
> . The SQL to do the latter is approximately:
>
> CREATE TABLE worker (
> worker_id INTEGER PRIMARY KEY,
> b TEXT
> );
>
> CREATE TABLE work_history (
> worker_id INTEGER,
> c INTEGER,
> d TEXT,
> CONSTRAINT FOREIGN KEY (worker_id) REFERENCING worker (worker_id)
> );
>
> You would have a record in "worker" for each distinct "worker_id" and that
> record contains all the details that aren't part of the work history. Then
> you
> have a record in "work_history" for each record that would have been in the
> inner table of "worker" had it existed, and you have the added "worker_id"
> field
> in "work_history" for every history row that would refer to the same
> worker.
> Having the same "worker_id" values in both sibling tables tells the RDBMS
> which
> rows in the 2 tables correspond to each other.
>
> -- Darren Duncan
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
