O.K. I think I am starting to get the idea. It is just so foreign for me to organize things this way. A master work history table for all cases almost seems confusing. It will just take a bit of adjustment for me to "trust" the database way of doing things. Text files organized in the way I described has always made it easy for me to figure things out when there was a problem.
I will give this a try. Thanks very much for all the help. On Fri, Oct 30, 2009 at 4:04 PM, Darren Duncan <dar...@darrenduncan.net>wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
