| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2004-02-25 : 08:36:28
|
Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities. tbEstimatedProjectsReference (PK) | Name | City | Postal |...-----------------------------------------------------------1 | Some Project | Niagra Falls | N8E7J5 | ....tbAwardedProjectsProject (PK) | Reference -------------------------1001 | 1tbProjectTeamProject (PK)| Login (PK) | Activity (PK)-----------------------------------------1001 | jsmith | DetailertbEmployees Login (PK) | First | Last |......----------------------------------jsmith | Jim | Smith |.....tbDepartmentListingLogin | DeptCode---------------------jsmith | ENGtbDepartmentsCode | Department------------------------ENG | EngineeringtblDepartmentActivitiesCode (PK) | Activity (PK)----------------------ENG | EngineeringENG | Detailer Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?Mike B |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-25 : 09:04:20
|
| re tblprojectteam...what does "login" on this table denote....it's manager?, or a member doing a particular job?what happens when jsmith moves from detailer to engineering?....does that become a totally new projectteammember?...because that is the essence of what you are saying (in this case)It is not a bad thing to have a table, where the entire record is the primary key....it is unusual, but sometimes necessary.Can you describe in english the relationships you are trying to model. this seems to be the reverse advice that is normally given...ie supply DDL, and sample data...but supplying both is best? |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 09:36:01
|
quote:
Originally posted by AndrewMurphy
re tblprojectteam...what does "login" on this table denote....it's manager?, or a member doing a particular job?
A member doing a particular activity.quote:
what happens when jsmith moves from detailer to engineering?....does that become a totally new projectteammember?...because that is the essence of what you are saying (in this case)
That is the reason for the activity field. It is possible one person can do more then one activity, or a person could get promoted. We still need to know who did what on what project.tbProjectTeamProject (PK)| Login (PK) | Activity (PK)-----------------------------------------1001 | jsmith | Detailer1001 | jsmith | Engineer.................................1923 | jsmith | Manager // Promotion? quote:
It is not a bad thing to have a table, where the entire record is the primary key....it is unusual, but sometimes necessary.
I seem to have quite a few tables with this occuringquote:
Can you describe in english the relationships you are trying to model. this seems to be the reverse advice that is normally given...ie supply DDL, and sample data...but supplying both is best?
I can try:An estimate is prepared, if and only if the project was awarded to us, we assign that project a number (1001). Next we assign the project team to an activity, or multiple activities, within their respected departments.Ambiguous?Mike B |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-25 : 16:47:19
|
| just glances at it without knowing your needs too much, it actually looks really good to me.- Jeff |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 17:00:58
|
quote:
Originally posted by jsmith8858
just glances at it without knowing your needs too much, it actually looks really good to me.- Jeff
Thanks, you should see the rest of it. :) Mike B |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-25 : 17:09:42
|
| Mike,Technically, you have normalised a database when it is in 1NF (A single value of a single type in a attribute). The other NF's are to remove update anomalies.It is not unusual at all to have the all column as the key.. How would you do a simple many-to-many table without it?What is the deal with using the "Reference" column for the PK with an EstimatedProject? I can see you are trying to separate "real" from "possible" projects, but since you are using the "Reference" column to obtain data from the "possible" table to actual supply data to the "real" project, that whole table seems redundant. Perhaps move all non-key column from the "Possible" projects table to the Projects table, add a new column "ProjectType" with values "Possible", "Real" (or whatever). Then create views around that new column to distinguish them ... Just a suggestion...DavidM"SQL-3 is an abomination.." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-02-25 : 17:13:00
|
| Mike, I think it's pretty normal to have all the columns make up the Primary Key of a junction table like yours (used to define many-to-many relationships). Now if we were talking about a table with more attributes, like a Customer table, or your tbEstimatedProjects then it would probably be out of line.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 20:40:28
|
quote:
Originally posted by byrmolWhat is the deal with using the "Reference" column for the PK with an EstimatedProject? I can see you are trying to separate "real" from "possible" projects, but since you are using the "Reference" column to obtain data from the "possible" table to actual supply data to the "real" project, that whole table seems redundant. DavidM"SQL-3 is an abomination.."
Actually, I have removed the tbAwardedProjects, placed the Project field into the estimated projects, set a unique constraint and index on this column and distinquish the "real" from "possible" by checking if this field is NULL. This works in SQL Server 7. I have tried it. This does raise another question: Why can a field with the unique constraint be null? Wouldn't two rows where this field is null go against the theory of "uniqueness"? What is NULL, is it nothing or 0?Mike B |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 20:59:06
|
| To me this design actually looks excellent except for the tbAwardedProjects-table which you have removed yourself. I have always had a thing for identity-fields as PK's and have found them alot easier to work with than the combined keys you are using but that's just me...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 21:12:07
|
quote:
Originally posted by Lumbago
To me this design actually looks excellent except for the tbAwardedProjects-table which you have removed yourself. I have always had a thing for identity-fields as PK's and have found them alot easier to work with than the combined keys you are using but that's just me...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand"
Hehehe, funny you mention this I was just going to post another question. Is the first way I had this with the two tables, tbEstimatedProjects and tbAwardedProjects really all that bad? What if I were to expand the awarded projects table even further?tbEstimatedProjectsReference (PK) | Name | City | Postal |...-----------------------------------------------------------1 | Some Project | Niagra Falls | N8E7J5 | ....2 | New House | Toronto | NNNNNN | ....3 | New Garage | Hamilton | nnnnnn | ....tbAwardedProjectsProject (PK) | Reference | AwardDate--------------------------------------1001 | 1 | 02/25/04 The two tables as opposed to one table:tbEstimatedProjectsReference (PK) | Project | AwardDate |Name | City ------------------------------------------------------1 | 10001 | 02/25/04 | Some Project | Niagra Falls2 | | | New House | Toronto3 | | | New Garage | Hamilton The second table will have alot of null fields ?Your thoughts?Mike B |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 21:16:09
|
| Well, my advice is that if an (estimated) project can have more than one awarddate you would need the additional table, if not then it would be better to have it in tbEstimatedProjects--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 21:20:58
|
quote:
Originally posted by Lumbago
Well, my advice is that if an (estimated) project can have more than one awarddate you would need the additional table, if not then it would be better to have it in tbEstimatedProjects--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand"
Very quick replies, I hope I am an expert one day and you need advice. I will be there for ya! :) heheMike B |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-25 : 21:27:23
|
quote:
Originally posted by AjarnMark
Mike, I think it's pretty normal to have all the columns make up the Primary Key of a junction table like yours (used to define many-to-many relationships).
What defines a many to many relationship. I was always thinking that there are many employees to one project (n:1) but as the tables are concerned, there are many employees to many projects (n:n). So this would mean that the tbProjectTeam is a "Junction" table for the "Many Employees" to the "Many Projects"?hmmm, like I said, I am a beginner, I am just doing what I think makes sence visually, terminology is not my forte!"I have no formal training" (William Hung, American Idol)Mike B |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-25 : 21:58:15
|
quote:
"I have no formal training" (William Hung, American Idol)
Great quote !!re-looking at your original schema, at first I agreed with David but if you really want to make sure you only assign a Project# when a project is no longer estimated but finalized, and you wish for the key of your projects table to be Project# (which makes sense), then I think it MIGHT make sense to leave it as you had it. it really might be a judgement call at this point ...but if you wish to store key info in the "estimated" table and not repeat it in the projects table, you need to make sure your workflow will ALWAYS be that a project is estimated first, and then moved into the projects table. and of course, you will need to join to your estimates table for info you don't wish to store redunantly such as project name and all that.I suspect you will have many more columns to add to the projects table since there are probably many things you can finallize and store about a project after it is no longer just an estimate.As for many-to-many tables, the classic example is "People" and "Skills". A person can have many skills; many people can have the same skill. you can't put a Skill column in the People table, and you can't put a Person column in the Skills table, because neither will allow for storing multiple values. the solution is to create a junction table, which might be called "PeopleSkills", with a PK of Person, Skill. In this table is where you assign people to skills, and you make the PK both columns to ensure a skill is only assinged to a person once.I strongly disagree with the notion of using identities as PK's for junction tables, no offense intended to Lumbago ... if the PK of the table is very clearly 2 columns logically, then I feel you should do it physically as well.- Jeff |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 22:11:38
|
| Jeff: It really depends, but if it's a pure junction-table I agree...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-02-26 : 14:57:48
|
| Jeff, great description of many-to-many relationships. Even though I have often used Identities (and no, let's not re-re-re-re-review the debate on that subject) junction tables are the place I always use real columns for the PK because it just makes so much sense to me, and it automatically prevents duplicate entries (Mark's hobbies: SQL, Goldwings, SQL, tennis, SQL, etc.)I'd like to add that it is possible that your junction table might have non-key attributes of its own. For example a billing database for consultants. Suppose as a consultant, I bill different rates for the different roles I play. Perhaps as a Designer I bill $50/hour, as a Developer I bill $60/hour, and as a Trainer I bill $35/hour. And a different consultant bills different rates for the same roles. Then the junction table would look something like:ConsultantXRole---------------ConsultantID (PK)RoleID (PK)BillRate--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
|
Am I taking this "normalization" too far?