Hi Dave
Here is a simple technique to use for data design. Ask yourself (or your
users) what are all the people, places and things we need to keep track of.
Think at the level of "People" rather than "First Name", "Surname" etc. Put
each one on a piece of paper and pin it to the wall.
Starting with the first and second ask "Can one of the first, have more than
one of the second?" "Can one of the second have more than one of the first?"
- If the answer is yes and yes, you need another table which combines first
and second
- If the answer is yes and no, you need to put a foreign key on the one
sheet. move on to the next combination (first and third piece of paper)
- If the answer is no and no, you might want to consider combining the two
pieces of paper.
Here is an example.
Say the first one was instructors, and the second courses. "Can one
instructor have more than one course" - Yes. "Can one course have more than
one instructor?" - Yes. You need another table for Instructors/Courses.
Say the first one was instructors and the third one faculty. "Can one
instructor have more than one faculty?" - No. "Can one faculty have more
than one instructor?" - Yes. Add a foreign key to the instructor sheet for
faculty number. It would not make sense to do it the other way around.
Having an instructor number in faculty as there are many instructors in each
faculty. You would need to record many numbers in a single faculty.
Say the first one was instructors and the fourth one salary. "Can the
instructor have more than one salary?" - No. "Can a salary be paid to more
than one instructor?" - No. In this case, a salary should be part of the
instructor table (called an attribute of Instructor). Put salary on the
instructor sheet and throw away the salary sheet.
Work through the paper starting at 1:1, 1:2, 1:3 etc until you reach the
end. Add new sheets as you go. When you reach the end, go back and start at
2:3, 2:4 etc.
When you have covered all possible combinations, you will have the entities
(or sheets of paper) sorted. On each sheet of paper add the attributes. On
Instructors it will probably be Instructor number, First Name, Surname,
Phone, DOB etc. Each sheet becomes a table.
This is a good starting point. There will always be additional entities and
attributes to add, but it will give you a good basic starting point. One
extra in the example above may relate to salary. You may not only want to
record current salary, but create a salary history. This would mean one
instructor can have many salaries (over time). You may actually need a
separate salary table.
Hope the technique helps.
Neville Turbit
www.projectperfect.com.au