Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  1. Add user to a project
  2. Assign user role
  3. Assign user department
  4. Create department (will this have to include creation of another table?)
  5. 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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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:

enter image description here

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.

enter image description here

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:

  • Resolving many-to-many relationships into intersection tables
  • Normalizing your tables

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:

enter image description here

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.

share|improve this answer
    
I think that helped enough.. thanks ! –  Brian Apr 7 '13 at 14:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.