Weak entity with a many-to-many relationship with its owner

database-design

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

Best Answer

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.

Related Solutions

Many to Many and Weak Entities

I think you can, using a "diamond" relationship diagram:

diagram

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;

Postgresql – Database modelling : Creating a model for restaurant application which gives statistics

Save all of the raw ratings (i.e. individual user inputs) with the date on which the rating was recorded.

Then you can calculate all of your weekly, monthly and yearly statistics based on the raw data each night. These rolled up statistics should also be dated (with a date range). In this way you can also keep multiple weeks, months and years so that you can look at trends if you like.

This is often called a data warehouse. It is a common way of pre-calculating rolled-up statistics for quick and easy reporting.

When you get past the longest of your roll-ups (e.g. 1 year) you can start to throw away your raw transactional data if you're worried about how much space it takes up.

Best Answer

Related Solutions

Many to Many and Weak Entities

Postgresql – Database modelling : Creating a model for restaurant application which gives statistics

Related Question