Ok, Im working on a simple database, purely as a training/familiarisation exercise.
The brief is as follows:
The database will be used to hold a contact telephone directory for a large organisation. The information required to be held is this:
The 'top level' of the directory will be a simple A-Z listing. Each individual listing in this list will have a sub-record containing
'title'
'text1'
'text2'
'text3'
'text4'
'telephone'
'comment1'
'comment2'
The records themselves will not contain information relating to specific individuals, rather to the post itself. For example, a top level entry of say 'Personnel Section' will have sub-records of 'Personnel Clerk', 'Personnel Manager' etc. This is because each post is often carried out by different people.
The entries in the top level list are guaranteed to be unique; the sub-record titles are *not* completely unique. For example, it may have top level entries of 'Personnel Section' and 'Finance Section'. Within each of these ie. the record title, both sections may have an entry for 'Department Manager'.
My initial thoughts for the table layout are:
TopLevelTable:
Department Name -> Primary Key
RecordTable:
Department -> Lookup to Department Name in TopLevelTable
Title
Text1
Text2
Text3
Text4
Telephone
Comment1
Comment2
If this layout is realistic, Im wondering what primary key to use for the record table, and is it correct to use a lookup to the Department Name in the RecordTable or not. Some postings I have seen here seem to indicate thats not good practice.
Including all of the fields in a single table doesn't seem the right idea, as the DepartmentName would then be duplicated many times. And of course, using a lookup of some type seems more logical so that mis-spelling would be reduced in the Department field of the RecordTable.
With these points in mind, is the above layout the correct way to go about it, or should I be thinking differently ?