speed difference: one large or 5 smaller tables

[Jesper F]

I'm designing a database with background information about
a group of people. There's large amount of info about each
adding up about 130 variables. There are in fact this many
and I know how to normalize a db.
Is there any advantage in breaking up this table to say 5
smaller ones?
The 130 varibles could be grouped in "Client
data", "Social info", "Work info" and so on, so the
smaller tables could be grouped liked that and probably
displayed on a tab each in a form.
Is one large table slower than 5 seperate ones?
Thanks for any help.

 

[Tim Ferguson]

adding up about 130 variables. There are in fact this many
and I know how to normalize a db.
Is there any advantage in breaking up this table to say 5
smaller ones?

Ummmm: if you know how to 'normalise a db', then you will know that 130
fields is just dying for further normalisation.

Seriously, it is uncommon for tables to need more than twenty or so
columns. JV here (I think) has the record for sixty fields, and that was
just once in an entire career of consultancy and design.

At a wild guess, you might want to look at "At Your Survey" by our own
Duane Hookom.

B Wishes


Tim F

 

[Jesper F]

Ummmm: if you know how to 'normalise a db', then you will
know that 130

fields is just dying for further normalisation.
Seriously, it is uncommon for tables to need more than twenty or so
columns.

Thanks, I've taken over a database and I'm trying to get
the field number down. However it's patient data and
they're registering an awful lot at one point in time.
I can find a few places for further normalization but I'll
won't get it below 100.
Is there any speed difference between having one large and
4 linked tables or doesn't it matter?

 

[Duane Hookom]

I would have to agree regarding the normalization issue. I might keep all
the information in all one table rather than multiple tables that are all
related one-to-one. I could see an issue with multiple users each
responsible with editing their own fields in the same record. For instance,
if some people edit the Social information while others edit the Work
information there may be a problem. You would minimize this multiuser issue
by normalizing or breaking up a table into smaller, narrower tables.

 

[Bas Cost Budde]

I would have to agree regarding the normalization issue. I might keep all
the information in all one table rather than multiple tables that are all
related one-to-one. I could see an issue with multiple users each
responsible with editing their own fields in the same record. For instance,
if some people edit the Social information while others edit the Work
information there may be a problem. You would minimize this multiuser issue
by normalizing or breaking up a table into smaller, narrower tables.

At times I do split up this amount of information, especially if it's
not that much procedural (i.e. taking individual measures, coding,
handling, whatevers) by considering every piece of information a kind of
address to the person. Patient.

I have three tables, then:

Patient (patientID, lookup name)
AddressType (typeName, optional description, optional datatype if you
insist)
PatientAddress (patientID, typeName, typeIndex, typeValue)

I have added typeIndex because a person can have more values for one type.

Does that help?

 

[John Vinson]

I'm designing a database with background information about
a group of people. There's large amount of info about each
adding up about 130 variables. There are in fact this many
and I know how to normalize a db.
Is there any advantage in breaking up this table to say 5
smaller ones?
The 130 varibles could be grouped in "Client
data", "Social info", "Work info" and so on, so the
smaller tables could be grouped liked that and probably
displayed on a tab each in a form.
Is one large table slower than 5 seperate ones?
Thanks for any help.

I have to agree with Tim, Duane, and Bas - I betcha this can be
decomposed into one or several one-to-many relationships. Work info
for instance - some people have two or more jobs; I'm not sure what
social info constitutes, but it might be splittable as well.

That said - the hazard of one large table isn't so much the hard 255
field limit, but the much sneakier 2000 byte limit. You can *create* a
table with 130 Text fields, each 20 bytes long - but as soon as you
try to create a record using 2000 or more bytes, you'll get an error.

Splitting the table into one-to-one relationships will avoid this
error in any individual table. Performance *will* be negatively
impacted however; joins are simply slower than a single table lookup.

 

[Jesper F]

I have to agree with Tim, Duane, and Bas - I betcha this
can be decomposed into one or several one-to-many
relationships.

Okay I'm sensing an agreement on this. I'll make sure to
normalize it whereever I can.
Thanks a lot for your help all of you

 

[Scott Loupin]

Jesper,

I'm in a similar situation (mental health client billing database), I
divided up information by security level (for example the data clerk needs
to know address and demographic info, and input billing tickets, but has no
need to see client notes, etc.)

I found no speed difference on the front end, but found I saved time when I
had to edit the tables, we use a networked database with multiple front
ends.

HTH

Scott

 

[Tim Ferguson]

Okay I'm sensing an agreement on this. I'll make sure to
normalize it whereever I can.

If it's anything like the medical databases I've seen, then there are
things like this:

GP_Name (replace GP with FamilyPhysician if you're not in UK)
GP_Address
GP_Telephone

and later
Solicitor_Name
Solicitor_Address
and even
NextOfKin_Name
NextOfKin_Address
etc, etc.

There are also things like

Diagnosis_1
ICDCode_1
Diagnosis_2
ICDCode_2
and/ or
Prescription_1
Prescription_2
Prescription_3

and quite often this:
WorkingStatus
SpouseWorkingStatus

and then loads of these
Smoker y/n
TakesExercise y/n
FamHistEyesight y/n
FamHistDiabetes y/n
FamHistHypertn y/n

and so on. I think you get the point

B Wishes


Tim F

 

[Duane Hookom]

You must have seen the same tables that I have seen in "canned" medical
applications!