Split table?

[FNey]

A db for an organization. I have all member data in 1 table with 177 fields ( all 1:1)and possibly growing. (350-1000+ member records).1:M fields already in different tables. Member data could be broken down into smaller tables (i.e. new members, attendance, home info, discharge ... etc). Am I better off sticking with current table or breaking it up into smaller tables even though they will all be 1:1 ?

[ranman256]

1 table for people. they can be members/visitors/etc.
all sub tables feed from that 1:M, attendance, etc

[Micron]

177 fields, one for each member? OR 177 fields of data on EACH member? Based on your post, I'm going with the former, although you do mention 1:M (one to many?). To me, that's what 177 fields ( all 1:1)and possibly growing means. Data that changes, such as member lists should NEVER be arranged in columns. Only basic data which corresponds directly to an entity should be in a table. Everything else should be in related tables. For example, tblMembers contains only names, contact info, addresses, etc. - that which relates only to the member. If you want to relate that to attendance (or some other aspect) you need a separate tblAttendance. As for your concern about size, 2GB is the current Access file limit. The speed at which you can process info has more to do with how you have related data and if the proper fields are indexed. Sorry if I've drawn the wrong conclusion, in which case I see no sense in having a "new members" table. What do you do when they are no longer "new"? Don't see the sense in dividing into alphabetical chunks either. As mentioned, the db size limit is 2GB, minus overhead requirements. Whether you have all of that in one table or many should not have much effect on performance. Structure and design of tables, queries is more important.

[JoeM]

You say that you have a "1-1" relationship with all your data, but do you have a lot of blank values in your table (i.e. it is really 0 or exactly 1 record for each member)? A lot of blanks often indicates that those value should not be in that table, but a separate table (that will have less records than the original table).

Another piece of advice is to try to split up the fields into multiple tables that make sense. ranman256 alluded to this, i.e. one table for members, one for attendance information, etc.

In designing tables, it is good to try to follow the Rules of Normalization. I believe the general rule of thumb is to try to get to at least the 3rd normal form when designing your database. Here is an article that discusses Rules of Normalization and other database design concepts: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[ranman256]

if you have 177 fields for each member , you are designed wrong.
the 177 items should be a record (sub tbl) for each member. 1:M
this subtable will be searchable this way.

My database held 2 million records. So it should be a problem for you.