Need help w/ table design
I have a question about table design (in conjunction w/ form design).
Although this question is focusing primarily on table design, I'd like to
start out w/
providing information about the form first.
I'm working on a prototype for a Scorecard (form).
On the final form, I envision to have the following:
- No textual data and no numeric information
- Instead I want dozens of "colored boxes" (text boxes).
- These boxes are linked to fields that store numbers such as "25, 50, 75,
100".
- The numbers correspond to %-levels of a) achieved tasks or b) unfinished
tasks or
c) tasks behind schedule... or whatever I'm tracking
- A "green box" (100%) reflects completion... a "red box" will raise concern
(at least it should)...
- I then could click on the "red box" (OnClick event) and will bring up the
underlying data (e.g. 25%) plus more descriptive information such as task
issue, delays, etc....
- Again, I might want dozens or even hundreds of these boxes (very small in
size) on a single form. The large number of small boxes would be grouped
into a) processes, b) years, c) quarters, etc.
Okay, I hope I was able to provide a good "mental picture" of the form
product (just in case though, I have attached a small JPG that illustrates
the prototype).
Now onto the actual question... "How to construct a proper table?"
With the current db draft, I ended up creating the following:
- 1 table which has...
- 1 record which has ...
- right now, 24 fields... each task has a field name... and, as stated
before, I might end up with hundreds of tasks... resulting in hundreds of
fields
Managing such table (w/ hundreds of fields in e.g. a single) seems simply
ridiculous and impossible... and goes against any logical database design,
right?
So, my question is:
How should I set up the table so that I have only a few fields (e.g. "Task",
"Year", "Quarter") and hundreds of records...
in conjunction with the form...
If I had a record for each task (which is logical db design),
I couldn't show all records (and their overall summary e.g. "25, 50, 75,
100")
on a single form, right?
Wow, I hope this is not too confusing... if you feel comfortable opening up
the JPG,
I believe this thread would make much more sense.
I appreciate any help that would enable me to make some progress on this.
Thanks,
Tom
|