Is this right?

[Cswenson]

I am a volunteer for a school district to build an Access database for tracking visits by the administration to schools in the system. I would appreciate responses about the RIGHT method to construct the database. I have seen too many stupid database designs and don't want anyone looking at this next year and thinking it is non-standard.

For the explanation I have pared down the tables to the essence:
Table: Schools
Fields: SchoolCode, SchoolName, SchoolPrincipal
Table: Visits
Fields: VisitID, VSchoolCode, VisitDate, VSchoolName, VSchoolPrincipal

Relationship: SchoolCode (one-to-many) VSchoolCode

SelectQuery: AddVisit
All fields except VisitID and SchoolCode

Form on AddVisit:
Fields: VSchoolCode, VisitDate, VSchoolName, VSchoolPrincipal

When the VSchoolCode is entered on the form the SchoolName and schoolPrincipal are automatically filled in by a VB Event:
VSchoolCode field Event (after update)
VB code: VSchoolName=SchoolName
VSchoolPrincipal=SchoolPrincipal

This means that the school and principal at the time of the visit is recorded and stored. When a new principal is assigned in Schools, the old visits will NOT be updated to show a new principal name. The principal at the time of the visit remains in the visit record. (It may seem funny to also store the school name but some schools do occasionally get renamed.)

The Schools table has under 100 records and the Visits table will have under 1000 record. (Space and speed are less important than readability and clarity.) I used VB only because I couldn't figure out how to make this work in an elementary way. Would this design make sense to an Access expert looking at this next year?

 

[bradcccs]

Looks quite well structured to me (but I am no expert).

One point to consider:

As the school name may change, all reports etc will have to be based on SchoolCode (I assume PK). I would seriously consider adding an autonumber ID key in order to avoid dealing with text school codes (particularly if they contain "special" characters) - I find where statements much easier with integers rather than text, but that may be just me.

IF you are recording any further information in relation to SchoolPrincipal, then I would extend your tables / relationships to account for this


HTH

Brad.

 

[Len Boorman]

When you ask "Is this right" it is a difficult question. To a degree it is right if it is right for you and your situation.

Personally if there was the likelyhood of Name and Principal changing I might be inclined to consider this in the design so that I do not duplicate data.

The School table perhaps should include From and To date fields. Similarly the Principle table.

This will add a level of complication since the School Name and Principal will be dependant upon the date of the visit and you will actually be retaining some history of the school.

I have been forced down this route in a previous existance when setting up a database to record Health and Safety activities. Here it was required that Dept Managers were recorded and over time things change. I did adopt the method above and it did work fine but it did add a level of complication to reporting particularly.

Len B

 

[bradcccs]

Pat,

I Know better than to question your point of view but . . .

Given that Cswenson mentioned that the principal and schoolnames can change between visits, wouldn't it be necessary to store the SchoolName and Principal variables at the time of the visit?

Whilst I realise that this is duplicating data (majority of the time) I would have thought that the possible variation calls for this by design. Is this not similar to storing current price in quote / sales tracking etc?

<He asks sheepishly>

Brad