Advice: Coding multiple departments

[TapeGun007]

I do not proclaim to be a db expert, so I'm just looking for some good advice here.

 

I will have multiple departments that I want to be managed on the website.  Each department will only simply contain who is in charge, and all the members of that department.  In the past, I've stored all this in one table like "Departments" or whatever. 

 

Since each department is basically a cookie cutter of every other department, I've thought about creating a table for each department instead.  So each table name would be like "department_Hardware", "department_Software".  This way if we shut down a department, all I have to do is delete that table.

 

Which is better?

[ignace]

You should have a table named departments and a members table that holds a foreign key to the department or if one member can be part of many departments, create an extra table that holds a reference to both department and member.

 

You avoid duplication and ease maintenance and querying. Removing a department is as easy as just removing the record. INNODB even allows you to set certain rules on foreign keys, when you do. And moving members to a different department is as easy as just performing an UPDATE-statement.

[TapeGun007]

Thank you very much.  The more I had went back and thought about it and then reread your post, the more I know that you are right.  Again, I'm a bit new to this... well... the more complex db's now.  Always learning something new.

[Psycho]

I concur with ignace. To add a little more clarity here are some examples of table structures you could use (fk) = foreign key.

Table: Departments
Fields:
- dept_id
- dept_name

Table: members
- member_id
- dept_id (fk)
- member_name
- member_title
- department_head (boolean)

Table: hardware
- hardware_id
- dept_id (fk)
- member_id (fk)
- asset_tag
- hardware_description
- cpu
- memory
- os

Table: software
- software_id
- hardware_id (fk)
- dept_id (fk)
- software_description

 

Note: for something like software it may make sense to have a separate table to list out the individual software titles and link them using a foreign key to the software table. This ensures you can get a list of all the people/machines running "Office 2010" for example.