The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

DB Design Catigorization

I'm working on a new product and a bit out of my element dealing with database design.  Hopefully someone here can lend me a hand.

I have a concept, say an event, that I need to categorize.  Lets say the categories are planned, completed, missed.  Each categorization needs to record some independent and distinct set of data.  The categories do not form any sort of inheritance hierarchy.

Currently I have a table for each, so Planned, Completed, and Missed tables.  I plan to have the index unique in all three tables.  It seems like the risks are that I could lose an Event or I could have an Event in two places at once (though this is a violation of the constraints).

Is there a typical method of handling this situation.  Thanks.
SF
Thursday, July 17, 2008
 
 
Wrap up your moves across tables in transactions and you shouldn't lose data.  (It is sort of important to many applications using databases that they are as ACID as they claim to be.)

How unique/different are we talking?  If they're horses of a different color, 3 tables will work fine.

If the three types of events are only slightly different, I'd stick them all in the same table, put in a type column, and just nil out any inappropriate columns.  For example, at the day job, we might have PublishedWidgets and DraftWidgets, where publishing a Widget causes you to lose 2 columns and gain 3, out of a number substantially greater than 3. 

Close enough -- they can both reside in the same table.  It greatly simplifies both the mental model for programmers (all Widgets are in the Widgets table) and often results in much cleaner SQL.  For example, the (reversible) task of publishing a widget becomes a single, easy to understand UPDATE.
Patrick McKenzie (Bingo Card Creator) Send private email
Thursday, July 17, 2008
 
 
Look up "generalization specialization relational design" on the web. You should get a few ideas on how others have handled this kind of design issue.

It's tough to know how much or how little effort you should put into really learning DB design.  If your product is the beginning of something that will turn into a major complex DB,  then the more you learn up front the less work you will have to redo later.  There is a lot to learn, despite how easy it all seems at first.

If, on the other hand, the DB design aspects of your product are fairly trivial, then learning a lot of DB design will probably just slow you down.

I've seen people who have learned too little or too much DB design, and lived to regret their choices.
Walter Mitty Send private email
Thursday, July 17, 2008
 
 
Mr. McKenzie:  Thank you for your help.  Unfortunately, an STI like design doesn't work for my needs in this case.  Different information is tracked in each case, with no overlap. 

Mr. Mitty:  Thank you for the search term.  That is always one of the hardest parts of learning something new.  I expect my product to have an increasingly complex DB structure.  I know that I have to learn more about DB design going forward to be successful.
SF
Thursday, July 17, 2008
 
 
To me the category of the event is simply an information field, potentially denoting the status of the event  - what happens when a planned event becomes complete ? do you remove the "planned" row and put a row into the "completed" ? Is the data specific to "planned" ever needed after the event is completed ? (say, to help plan the same "event" next year etc?).

Based on the information here I'd be tempted to have an event table with all events in, create a column that holds the status planned/completed etc and then child tables to hold the specific data (e.g. event_planning, event_complete etc).
That way you can keep all the data going fowards (should it be needed) and you only have one place to look to find your events.
Mark
Thursday, July 17, 2008
 
 
A planned event only exists before the event occurs.  After that, it is no longer planned.  It was either attended or it was not attended.

When it is being planed, an assortment of data collecting methods are assigned to be completed.

A completed event has the data from these methods.

A missed event collects rational on why the event was missed for reporting purposes.

The planning data is only used to generate the forms necessary to gather the data.

A practical example:  a doctors office schedules a patient for a physical where they will take a TB test.  The scheduling of the TB test is determined because the patient needs it and isn't remarkable. 

The results of the TB test are important and get recorded if the patient completes the event.  If the pt misses the event, that information is required so they could be billed.
SF
Thursday, July 17, 2008
 
 
Why not one table for events, with related tables for planning and completion? For missed you might or might not need another table depending on how much information you collect?

CREATE TABLE EVENTS
(ID INTEGER NOT NULL,
 STATUS VARCHAR(8) NOT NULL,
  ...
);

CREATE TABLE PLANNING_DATA
(
 ID INTEGER NOT NULL,
 EVENT_ID INTEGER NOT NULL,
 ...
);

and so on. The planning data need not be purged
George Jansen Send private email
Thursday, July 17, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz