Table design questions

[Guest]

Hi Everyone,
Quick question. What would have others done to help reduce the required
number of fields for a table? I currently have several tables that contain
over 150 fields. and I'm considering using one field to keep the status of
several options for a field. I'm writing this program to allow users to enter
in Medical info for our clients. One of the fields in consideration is as
follows:
Mood (Name)
Normal (Checked)
Anxious (Checked)
Depressed
Euphoric
Other (Checked)
In the database it would be saved in a Char(5) field like so: 11001
I'm thinking of this technique becase if I breakout the options into there
own field I will have an extra 70-80 fields and I'm alread setting at 190
fields. ANy suggestions or comments. THanks for any help.
Michael

 

[Chris]

Hi Everyone,
Quick question. What would have others done to help reduce the required
number of fields for a table? I currently have several tables that contain
over 150 fields. and I'm considering using one field to keep the status of
several options for a field. I'm writing this program to allow users to enter
in Medical info for our clients. One of the fields in consideration is as
follows:
Mood (Name)
Normal (Checked)
Anxious (Checked)
Depressed
Euphoric
Other (Checked)
In the database it would be saved in a Char(5) field like so: 11001
I'm thinking of this technique becase if I breakout the options into there
own field I will have an extra 70-80 fields and I'm alread setting at 190
fields. ANy suggestions or comments. THanks for any help.
Michael

Well I'd avoid it if possible. You could do it as a relation table. so
you just add the needed items to the a subtable

SubTable - Mood:
MainTableID int
MoodType tinyint

So only the items that they actually checked are saved, this way you can
expand the list w/o expanding the table in the future.

If you are going to go about the way you were talking, I'd not use a
char(5) (what if you have to add on in the future) but instead do
bitmask. Save it as an integer (only 2 bytes instead of 5)

Mood (Name)
1 Normal (Checked)
2 Anxious (Checked)
4 Depressed
8 Euphoric
16 Other (Checked)

so in your example you'd save 19 in the field. And then just have to do
something like:

if CBool(MoodField And 1) then
'Normal Was Checked
end if

Hope it helps some.

Chris

 

[Cor Ligthert [MVP]]

Michael,

It is not easier for you direct, however you can break down your database
where every topic has its own table.

After done that, you can see how you can generalize it in a way that a Cat
and Fish are animals, so they can use the same table (you cannot use
polyphorisme in a current database so you have to double the fields where
that exist and use than for Cats and Fish the table animals with a field in
it "kind of animal").

I hope that this gives an idea

Cor

 

[Homer J Simpson]

Hi Everyone,
Quick question. What would have others done to help reduce the required
number of fields for a table? I currently have several tables that contain
over 150 fields. and I'm considering using one field to keep the status of
several options for a field. I'm writing this program to allow users to
enter
in Medical info for our clients. One of the fields in consideration is as
follows:
Mood (Name)
Normal (Checked)
Anxious (Checked)
Depressed
Euphoric
Other (Checked)
In the database it would be saved in a Char(5) field like so: 11001
I'm thinking of this technique becase if I breakout the options into there
own field I will have an extra 70-80 fields and I'm alread setting at 190
fields. ANy suggestions or comments. THanks for any help.

Excellent idea. Every field has a cost, and storing one bit in a field is
expensive. There's no good reason not to use a bitimage of these on/off
selections. You can even use 2 or 3 bits to store more complex selections.
For example, compass directions for streets (N,S,E,W,NE,NW,SE,SW) can be
stored in 3 bits (4 if you need unknown as an option).

BTW, indexing a single bit field is an evil notion. Indexing a field such as
you describe is a valid idea.