single table or multiple tables?

[Robert]

I’m creating a new access 2007 database, my question is simple. From a
functionality standpoint and taking database size into consideration. Is it
better to have one table with repeating field values or setup a second table
to hold these values using a lookup.
Example 1 single table
Fields
State -AR, AZ,…
Grad Schools UVA, UGA, Yale……(possibly 400+ different schools)
Under Grad Schools
Example2 multiple tables
Table 1-state (lookup table 2 field state)
table2, field-state (records-Ar, AZ….)
Table1 GradSchools………..

I have 40,000 records to import into this database, so I would like to
minimize field size.

 

[KARL DEWEY]

You did not present very much information - only two things - state & school
- these could be in one table.
What do you intend to use the data for? What else are you going to collect
on these records?

 

[Ken Sheridan]

Without a doubt two tables (at least!). By having a States table and
relating this to the Schools table on the State column with referential
integrity enforced only valid State values can be entered in Schools. With a
single table the same state could be entered differently in two rows as a
result of a simple typo. If you are recording the school's location solely
by state then having a State column in the Schools column as a foreign key is
fine, but if for instance you are also recording the location by City say
then you should also have a Cities table and a CityID foreign key in Schools
(not the city name as these can be duplicated). The Cities table would then
have a State column referencing the States table. The Schools table would
not have a State column as the sate is known via the relationships for
Schools to Cities to States.

You'll find a demo of how this sort of hierarchical location data can be
handled with combo boxes in forms while preserving normalized table
structures at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Pat Hartman \(MVP\)]

Since relational databases do not support repeating groups (they violate
first normal form), you will find it extremely difficult to work with them
in a real world application because there is no easy way to work with them.
Size doesn't enter into the picture although the normalized structure will
almost certainly be smaller since the text strings will be replaced by long
integers (4 bytes) and you won't have the baggage of multiple empty fields
which you would have to leave to accommodate the maximum set of values for a
repeating group.

Your example doesn't indicate that you actually have a repeating group.
Having a list of potentially 400 grad schools is not the same as trying to
figure out how many grad school slots to leave in a personnel record. The
latter is a repeating group, the former is not. Repeating groups can
usually be identified by their names. The names of individual items in a
repeating group will either include numbers such as GradSchool1,
GradSchool2, GradSchool3, etc or prefixes/suffixes such as OilExpense,
ElectricExpense, LawnCareExpense, etc. Sometimes the prefix/suffix must be
inferred as in Oil, Electric, LawnCare. That's where it gets tougher. You
need to be able to identify those items as a repeating group and get them
out into their own table.