Tell me more ×
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.
up vote 1 down vote favorite

Does this design satisfy 3NF, and can any improvements be made?

If it does not satisfy 3NF - why?

By improvements, I only care if it will seriously affect scalability, efficiency, etc - I'm not interested in personal preferences.

To add more detail, there can be many bids to a project and any employee can create a new bid. Unit price can be different for each project/bid.

enter image description here

share|improve this question
1  
Unless you're using some prehistoric database, you could use whole words for field names. What is 'Code?' – podiluska Oct 9 '12 at 7:13
Oh, Code? is irrelevant, and should be ignored for the sake of my question regarding 3NF – Doctor Oreo Oct 9 '12 at 8:16
Generally, you end up denormalizing for scalability often. – rfusca Nov 9 '12 at 1:20
Why do you prefix every table with tbl? That looks really strange. – a_horse_with_no_name Dec 8 '12 at 12:01
Your design suggests not only that there are bids per project but that an Employee can make many bids per Project and per Item. And that there is no notion (or you don't record it) of Bid-of-Employee-for-a-certain-Project. If that is your specifications, that part is fine (although this kind of many-to-may-to-many relationships usually need more attention of what you want to model and maybe splitting into more tables). – ypercube Dec 8 '12 at 12:06
show 2 more comments

3 Answers

up vote 2 down vote accepted

The normal forms apply to individual tables. Dependencies are semantic; they depend on what the data means. Normalizing by relying on column names alone is not very reliable.

  • TblClient is in 5NF.
  • TblEmployee might have a transitive dependency and be only in 2NF, if EmployeeNm->EmployeeRole.
  • TblItem might have a transitive dependency and be only in 2NF, if (ItemType, ItemDesc)->Size, or if ItemDesc->Size.
  • TblProject might have a transitive dependency and be only in 2NF, if (ProjectSite, EstBeginDt)->EstCompleteDt, or if (ProjectSite, EstBeginDt)->anything else.

Every one of these tables almost certainly needs an additional unique constraint on one or more columns. Otherwise you're likely to end up with data that looks like this.

ItemID  ItemType  ItemDesc                Size
--
245     Flange    Engine mounting flange  13mm
246     Flange    Engine mounting flange  13mm
247     Flange    Engine mounting flange  13mm
451     Flange    Engine mounting flange  13mm
457     Flange    Engine mounting flange  13mm
683     Flange    Engine mounting flange  13mm

That particular table needs an additional unique constraint on either {ItemType, ItemDesc} or on {ItemType, ItemDesc, Size}.

Reaching 3NF for every table doesn't necessarily mean you have a good design. You might well have missed critical requirements. If you missed a critical requirement, you're not likely to discover it by normalizing to 3NF or 5NF.

Whoever taught you to name all tables "Tbl"-something or "Lk"-something or "Xref"-something did you no favors. It's likely that person didn't understand logical data independence, and it's likely you don't understand it either. That's a big deal, because it's one of the main features of the relational model, and SQL supports it fairly well. Name tables for what they are, not for how you intend to use them today.

share|improve this answer
up vote 1 down vote

What's the relationship between items and projects?

Shouldn't you break off ContactNm and ContactPhone into a separate table and relate these to Client so they can be re-used on subsequent projects but you can still have new info on each project?

share|improve this answer
up vote 0 down vote

What is the relationship between Client and Project? A client can have many projects? but does that mean that the contact information for the project is part of the address information of the client or is the project contact information something completely separate?

share|improve this answer
Unless you have an answer to the question, and not just clarifying questions it's probably best to just leave comments on the question itself. – trygvis Dec 30 '12 at 23:28

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.