On Fri, Oct 30, 2009 at 2:41 AM, mark m <marksql...@gmail.com> 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.
Expand Darren's suggestion with the following --
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)
case_id INTEGER
);
CREATE TABLE cases (
case_id INTEGER PRIMARY KEY,
e TEXT
);
So, above I have added a table for cases where you can store your case
history, and then, in your work_history table, added a case_id FK.
>
> 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
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
