-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'd do it like this.
Counter is an AutoNumber data type.
Unique is a unique index (no duplicates).
You can change the length of each VARCHAR data type to suit your data.
CREATE TABLE Schools (
school_id COUNTER NOT NULL UNIQUE ,
school_name VARCHAR(30) NOT NULL PRIMARY KEY ,
school_city VARCHAR(35) NOT NULL ,
... etc. ...
)
It might be better to have the Primary Key as the school_name and
school_city, 'cuz there may be more than one "WalMart Elementary School"
;-) in different cities.
School_population should be in another table 'cuz it changes every year.
It would normally be a calculated column in a query that counts the
number of students. Since this db doesn't look like it will track each
individual student you could probably just use a table like this:
CREATE TABLE SchoolPopulations (
school_id LONG NOT NULL
REFERENCES Schools (school_id)
ON DELETE CASCADE ,
pop_begin_date DATE NOT NULL ,
pop_end_date DATE NOT NULL ,
school_population INT NOT NULL ,
CONSTRAINT PK_Pops PRIMARY KEY (school_id, pop_begin_date)
)
A table for the types of equipment:
CREATE TABLE EquipmentTypes (
equip_type COUNTER NOT NULL UNIQUE ,
equip_desc VARCHAR(30) NOT NULL PRIMARY KEY ,
)
Now a table that indicates which pieces of equipment go w/ each school:
CREATE TABLE SchoolEquipment (
equip_id COUNTER NOT NULL UNIQUE ,
school_id LONG NOT NULL
REFERENCES Schools (school_id)
ON DELETE CASCADE,
equip_type LONG NOT NULL
REFERENCES EquipmentTypes (equip_type) ,
equip_posn CHAR(4) NOT NULL
CHECK (equip_posn IN ('N', 'S', 'E', 'W', 'NE', 'NW', 'SE', 'SW') ,
CONSTRAINT PK_SchEqp PRIMARY KEY (school_id, equip_type, equip_posn)
)
Access doesn't like CHECK expressions in CREATE TABLE statements, this
is just an example. You can use this for the column's Validation Rule
property. Just use the "IN ('N', 'S', etc....)" expression in the
property.
For the equipment history I'm not quite sure, 'cuz you don't state the
purpose of this db; and I'm not sure what the Count per "t1", "t2" is.
Is t1, t2, etc. a time?
Here's a table that show the physical environment of the equipment per
date. "Meas" is short for measurement.
CREATE TABLE EquipEnviron (
equip_id LONG NOT NULL
REFERENCES SchoolEquipment (equip_id)
ON DELETE CASCADE ,
meas_date DATE NOT NULL ,
meas_posn CHAR(1) NOT NULL
CHECK (meas_posn IN ('L','R') ,
equip_height DOUBLE NOT NULL ,
equip_depth DOUBLE NOT NULL ,
CONSTRAINT PK_EqEnviron PRIMARY KEY (equip_id, meas_date)
)
meas_posn holds 'L' (Left) or 'R' (Right).
Put an Index on the meas_date.
I use Double data type for the height and depth so you can use decimal
values.
Here's the table for the count of children usage of equipment. The
column use_date contains both the date and the time. That way you can
include the time of the t1, t2, t3, etc. values. One of the drawbacks
of using a combined DateTime column is querying for a specific date.
You can't use something like this:
WHERE use_date = #1/1/2006#
You'd have to use this:
WHERE use_date BETWEEN #1/1/2006 00:00# AND #1/1/2006 23:59#
which is the expression for one day, w/ times from midnight to 1 minute
before midnight of the next day; IOW 23 hrs and 59 minutes in one day.
Some people would do something like this:
WHERE DatePart(use_date) = #1/1/2006#
but, this is slower than the BETWEEN 'cuz each value in use_date has to
be parsed by the DatePart() function, a complete table scan, instead of
the query using the use_date index, which is faster.
CREATE TABLE EquipUse (
equip_id LONG NOT NULL
REFERENCES SchoolEquipment (equip_id)
ON DELETE CASCADE ,
use_date DATE NOT NULL ,
child_count INT NOT NULL ,
CONSTRAINT PK_EquipUse PRIMARY KEY (equip_id, use_date)
)
Put an index on the use_date.
The ON DELETE CASCADE means if the "parent" table data row is deleted,
then the "child" table row is also deleted. IOW, if the
SchoolEquipment.equip_id '222' was deleted, all rows in any tables that
had the same equip_id number would be deleted.
HTH,
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only on this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRK60MoechKqOuFEgEQIVtACgn3tGYME4zRD4p2GJAFGiXO rC4gUAoJQQ
c8Jfh/hPIwuNbrYkiPBYho4u
=zoRW
-----END PGP SIGNATURE-----
Meaney wrote:
Hi,
I'm relatively new when using Access so bear with me.
I have an Excel file with 4 spreadsheets, each with multiple columns
Tbl.Schools
SchoolName
SchoolCity
SchoolPop
etc.
Tbl.Counts
SchoolName
EquipmentLoc (where in large playground is equipment located...N, S,
E,W)
EquipmentType (is it a slide, jungle gym, etc...there are about 60
different possibilities)
Count1 (60 sec count of how many children were on a piece of equipment
at t1)
Count2 (60 sec count of how many children were on same piece equipment
at t2)
Count3
Date
etc.
Tbl.Depth
SchoolName
EquipmentLoc (same as above)
EquipmentType (same as above)
EquipmentPos (what position on equipment is depth being measured. i.e
left side monkey bars or right)
Depth1 (measure how deep surface is below certain pieces equipment)
DateDepth1
Depth2
DateDepth2
Depth3
DateDepth3
etc
Tbl.Height
SchoolName
EquipmentLoc
EquipmentType
EquipmentPos
Height (measure how far distance is between certain pieces equipment
and ground)
DateHeight
I am having trouble grasping how to divide these four spreadsheets into
access tables in order that the data is properly normalized in 3NF,
while still allowing for proper relationships to be made in order to
ensure referential integrity between tables.
I find myself in a situation where I'm having to put SchoolName,
EquipmentLoc, EquipmentType and EquipmentPos in multiple tables; but if
these data appear in multiple locations in the db it implies it is not
properly normalized...right?
How should I arrange the data into tables so that they are properly
normalized? Any help in setting up these tables would be greatly
appreciated.
Chris
