I'm using SQL-Server 2012 and I would like to know how to solve this program.
So:
I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to the project (Project Table).
Now, I have another table, users, containing all the users on the system.
Each project has a different department (departments can be added – for example, hardware, software, accounting etc).
What I would like to solve is.. how would I do so as to create a new department and add employees to the project i.e. how will have the tables be structured, and what tables are to be created?
Steps
- Add user to a project
- Assign user role
- Assign user department
- Create department (will this have to include creation of another table?)
- Add users to that department (will this have to include creation of another table?)
How can can I solve this in terms of database design?
Best Answer
The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:
Step 1: Make an Entity-Type for Each Tangible Thing
Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:
Step 2: Identify Your Relationships
Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.
Step 3: Transform Your Logical Model Into a Physical Model
Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.
This involves at least two very important steps:
In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.
Your model with m:n relationships resolved would look like this:
Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.