Table Design

[allen davidson]

Hi, hope to get some views on this.

If I have a table representing an entity and that entity has 9 attributes
(eg, regions of the county where the entity operates) I would add 9 boolean
region fields.

Does that seem Ok.

Now I want to change my table and rather than 9 regions I want to map
oparations against 400 districts. Would you go with 400 booleans or would you
go with a one to many relationship to a regions table? My gut feeling is with
the regions table.

The 9 booleans seems reasonable to 400 does not, I cant see (but I may be
mising it) any help in Codd's Normal Forms to decide at what point yuo should
split out attribuites in this way.

Any thoughts?

 

[Beetle]

You're right in that 400 boolean fields would be *completely* unreasonable,
not to mention the fact that it can't be done because you can't put that many
fields in one table. In addition, your 9 boolean field design is incorrect
also.

You need additional tables for regions/districts. Plus, if a region/district
can
have more than on "entity", then you have a many-to-many relationship,
in which case you a third table to define each M2M relationship.

 

[Jerry Whittle]

If you are talking 9 versus 400 fields, the answer is neither.

400 won't work at Access only has a maximum of 255 fields in a table. Short
of a kludgy 1-1 join between multiple tables, it's a non-event.

As far as just 9, here's the question: You built all the forms, queries,
reports, macros and code based on 9 fields. But then someone adds a 10th
attribute that just MUST be in the database. Back to the drawling board! Then
6 months later here comes attribute #11. Better to create a robust design in
the first place.

Put it this way, if the data is actually the field name, your database isn't
structured properly.

There are times that it makes sense to de-normalize the table structure. For
example a case could be made for just having a PhoneNumber field with all the
different phone numbers OR individual PhoneHome, PhoneCell, PhoneWork, etc.,
fields.

 

[allen davidson]

Thanks all,

I particulary like
structured properly <<

That seems a good rule.

Regards
Allen