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
