Table Design

[Denise Walker via AccessMonster.com]

I'm looking for the best design for a database I'm creating.

I have a table that has 60 fields. The User wants to be able to bring up
client records that only use certain fields that pertain to that client.
One Client may only use 12 fields were another Client may only use 4 fields
and another Client may use 20 fields from this table.

Also, This 60 field table will be used by 3 small User groups which will
need to access similar sets of data at different times. No more that 12 to
15 users updating this table at one time. More (around 50 may be
previewing the data only)

1. Should I break up this large 60 field table into smaller tables with
logical groupings (client name address table, candidate table, background
verification table, skill table, rate table, etc.) and tie them all to a
primary key such as (client name, client id)?

2. If so, how would this affect the User groups accessing the table. I
would like the forms to load fast for them.

Thanks in advance for your help!
Denise

 

[Denise Walker via AccessMonster.com]

A little more clarification:

In the 60 field table, one candidate may have empty fields because those
fields do not pertain to the company. That's why the table has so many
fields.

I'm thinking that I may have to break this large table up into 30 company
tables and one candidate table with the company name and company id as the
key.

Then when the User pulls up a candidate, they only have candidate and
company info for that candidate. I'm thinking this will load the form
faster too if the query was just joining companytbl and candidate table.

Anything I'm missing??

Thanks,
Denise

 

[Tim Ferguson]

I have a table that has 60 fields.

That's not a table, it's a spreadsheet. It is pretty rare to need more
than twenty or so fields in a properly designed table.

1. Should I break up this large 60 field table into smaller tables with
logical groupings

Be careful what you mean by "logical groupings" -- there is a well
defined algorithm called normalisation that will help you convert this
spreadsheet into a database. Consult any standard textbook on db design.

2. If so, how would this affect the User groups accessing the table.
I would like the forms to load fast for them.

Forms design is separate from, and several stages later than, tables and
schema design. Get the schema nailed down and proven first, then think
about the User Interface. There are plenty of ways of making forms quick
or slow to load, and none of them involve crippling the schema.

Hope that helps


Tim F

 

[Denise Walker via AccessMonster.com]

Yes, that helped. I have 3 tables (company, candidate, customdata) all
tied to companyid.

Thank you!!