2

I have a database that keeps track of the hours worked by a contractor per week. I have one table for contractors that contains "con ID" as primary key. The other table Weekly Hours Tracker contains month day year (representing a particular week) and hours worked. Now from what I understand this table is a weak entity and relies on contractor table for its existence.

The relationship is defined as one contractor can work for many weeks and in one week there are many contractors working. So there is a Many to Many relationship.

So is the Weekly Hours Tracker a weak entity that has a many to many relationship with its owner entity Contractors?

Is following the correct way to represent the above tables?

(Bold attributes are keys)

Contractors Table

  • ConID, Name, HourlyRate, Address

Weekly Hours Tracker

  • ConID, Day, Month, Year, HoursWorked
1

Yes, your entity is OK, but I would recommend this :

  • Do not use a multiple fields for your date. Instead of Day, Month, Year, you should use DateWorked. Maybe you are already doing that but it doesn't reflect in your explanations. This way, your date will always be valid. You won't have to check if February 30, 2013 is a valid date or not.
  • I strongly recommend using only 1 field surrogate primary key like HoursID. You can create a unique index on ConID and DateWorked to validate it will not exist more than once.
  • You may add complexity now or later by using ProjectID and that project could also bind to a ClientID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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