If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design
Hi,
I am developing a database and I am nervous about the design. I am gathering information about organization departments. There are about 65 attributes for each department. Those are my fields. My question is should I make one table with the department name and all 65 attributes? Right now I grouped the attributes by general categories (tables) of dept size (no. of people, no. of printers, etc. are the fields), dept products (reports, surveys, manuals, etc), dept needs (Equipment, Money, Furniture) I have a department ID as a primary key in the main dept table and as a foreign key in the other tables. There is no primary key in the other tables. Is my current design okay or should it just be 1 big table? Thank you. |
#2
|
|||
|
|||
Database Design
Hi
I posted this recently on another question. It may help you with the design. Also keep asking yourself what will happen if you find other information to include. Can you hadle it without changing table structure. 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 "lil2009" wrote: Hi, I am developing a database and I am nervous about the design. I am gathering information about organization departments. There are about 65 attributes for each department. Those are my fields. My question is should I make one table with the department name and all 65 attributes? Right now I grouped the attributes by general categories (tables) of dept size (no. of people, no. of printers, etc. are the fields), dept products (reports, surveys, manuals, etc), dept needs (Equipment, Money, Furniture) I have a department ID as a primary key in the main dept table and as a foreign key in the other tables. There is no primary key in the other tables. Is my current design okay or should it just be 1 big table? Thank you. |
Thread Tools | |
Display Modes | |
|
|