MySQL Server Forum - Question about database design

Author

Question about database design

Anthony P.

2005-08-23, 3:23 am

Hello Everyone,

I've run into a problem that I can't seem to wrap my head around and I
need some help. I know this might not be the *exact* right place to ask
for help but since my problem *is* with databases and my database of
choice *is* mysql, I thought I'd start here.

Here's the problem:

I am attempting to create a representation of a paper form within a
database. The form basically looks like this:

GOAL:
The description of the overall goal to be met

OBJECTIVE:
- A list of several "objectives" that must be met in order to meet
the goal. These objectives are all in a numbered list on the paper
form.

Now, each objective must be met a certain amount of times within a
given period. Each time an objective is met, the user must check the
"Met" box that will be displayed on the side of the objective. Then,
the user must use several dropdown boxes that contain information on
how the objective was met (static list of ways), when it was met, where
it was met (static list of locations), what staff did in response
(static list of responses), and date/time. Once an objective has been
met the required amount of times for that period it will no longer be
displayed on the HTML representation of the form.

My problem isn't about entering the information into the HTML form but
rather how to create the tables in the database to hold the required
information. I know I'm going to need more than one table. But how
many? How can I best organize this information so that 1) it's
managable and 2) it's not redundent.

How would YOU address this problem? I truly appreciate any guidance
anyone can provide.

Thanks,
Anthony Papillion II

Alan Little

2005-08-23, 9:23 am

Carved in mystic runes upon the very living rock, the last words of
Anthony P. of mailing.database.mysql make plain:

> I am attempting to create a representation of a paper form within a
> database. The form basically looks like this:
>
>
> GOAL:
> The description of the overall goal to be met
>
> OBJECTIVE:
> - A list of several "objectives" that must be met in order to meet
> the goal. These objectives are all in a numbered list on the paper
> form.
>
> Now, each objective must be met a certain amount of times within a
> given period. Each time an objective is met, the user must check the
> "Met" box that will be displayed on the side of the objective. Then,
> the user must use several dropdown boxes that contain information on
> how the objective was met (static list of ways), when it was met, where
> it was met (static list of locations), what staff did in response
> (static list of responses), and date/time. Once an objective has been
> met the required amount of times for that period it will no longer be
> displayed on the HTML representation of the form.

CREATE TABLE goals (
GoalID int(11) NOT NULL auto_increment,
Description text
PRIMARY KEY (GoalID)
) TYPE=MyISAM;

CREATE TABLE objectives (
ObjID int(11) NOT NULL auto_increment,
GoalID int(11),
Description text,
ReqTimes tinyint(4)
) TYPE=MyISAM;

CREATE TABLE objmet (
ObjID int(11),
HowMet int(11),
WhenMet datetime,
WhereMet int(11),
StaffResponse int(11),
Something datetime
) TYPE=MyISAM;

CREATE TABLE howmet (
HowID int(11) NOT NULL auto_increment,
Description tinytext,
PRIMARY KEY (HowID)
) TYPE=MyISAM;

CREATE TABLE wheremet (
WhereID int(11) NOT NULL auto_increment,
Description tinytext,
PRIMARY KEY (WhereID)
) TYPE=MyISAM;

CREATE TABLE response (
ResponseID int(11) NOT NULL auto_increment,
Description tinytext,
PRIMARY KEY (ResponseID)
) TYPE=MyISAM;

On the objmet table, you said "when it was met" and "date/time". I don't
know what the difference is; that's what the "Something" field is, there.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

Alan Little

2005-08-23, 9:23 am

Carved in mystic runes upon the very living rock, the last words of Alan
Little of mailing.database.mysql make plain:

> CREATE TABLE howmet (
> HowID int(11) NOT NULL auto_increment,
> Description tinytext,
> PRIMARY KEY (HowID)
> ) TYPE=MyISAM;
>
> CREATE TABLE wheremet (
> WhereID int(11) NOT NULL auto_increment,
> Description tinytext,
> PRIMARY KEY (WhereID)
> ) TYPE=MyISAM;
>
> CREATE TABLE response (
> ResponseID int(11) NOT NULL auto_increment,
> Description tinytext,
> PRIMARY KEY (ResponseID)
> ) TYPE=MyISAM;

I forgot to mention, these three tables aren't really necessary; you
could just have the information coded into your program.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

Sponsored Links