Table Layout Question

[WonkeyDonkey]

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 ?

[Matrix]

Your design sounds complicated to me. You may want to think about them in a different way. Define the objects first, such as department, person, etc. Don't think which one is in a higher level, just relationship, and store the relationships in other tables.

How about like this:
[department]
id (Primary Key)
name
Text1
Text2
Text3
Text4
Telephone

[person]
id (Primary Key)
name
...

[role]
id (Primary Key)
name
....

The role table have records like:
1 -> clerk
2 -> manager
...

Position table stores all relationships, primary Key is (person_id, department_id, role_id).
[position]
person_id
department_id
role_id

[WonkeyDonkey]

Well there is no requirement for a person table here. The records point to a particular post with the Department.

I should explain the data a little more I think.

Each complete record should hold information about:

Department
Role
Text1
Text2
Text3
Text4
Telephone
Comment1
Comment2

There are no references to individuals as such.

The idea here is that, once completed, you should be able to select a given Department, and then see a list of all the Roles within that Department. Then you should be able to select a Role, and see the individual details for it.

None of the Text1-4 or Comment fields are guaranteed to contain data in every record. So in terms of Primary Keys, there is only really the Department and Role to consider. Considering the 'Role' may have the same data in more than one record, I doubt it can be a Primary Key on its own.

(Title should have read 'Role' in my previous posting - apologies for that).

I might be missing something here, but I don't see how I can achieve that given the layout you suggest.

Thinking of the data items as you suggest, there will be a number of Departments. Each Department will have one or more Roles. Each Role will contain a number of text items, telephone and comment fields.

I hope I've been a little clearer

[Matrix]

It seems easier now. How about just adding a field "id" as the PK to this table?

Department
Role
Text1
Text2
Text3
Text4
Telephone
Comment1
Comment2

If you want to manage the department and role easier, add tables for them as my previous reply said, then the Department and Role field will be id here, otherwise, use names.

you should be able to select a given Department, and then see a list of all the Roles within that Department. Then you should be able to select a Role, and see the individual details for it.

These are just different queries, no matter you have separated tables for department and role or not, it is easy to construct the SQL queries.

[WonkeyDonkey]

Ok, so if I go for:

MainTable:

ID Autonumber (PK)
Department - Lookup to Department table
Role
Text1
Text2
Text3
Text4
Telephone
Comment1
Comment2

Department:
Department Title (PK)

Presumably then its just a single relationship:

Department Title <One-to-many> Department Lookup ?

[Matrix]

If you have a department table, the field "department" in the main table should use ID (integer) instead of title.

[WonkeyDonkey]

Right okay, I shall give this a go today. Thanks for the help.

I shall more than likely be posting again once I get started!

:wink: