Relationships for tables.

[justin2008]

I have a little trouble coming up relationships for the following. If anyone could help, that would be great.

>>track customers and their personal information like name, address, and phone number
>>track payments made by customers to include amount paid, date, and show which project it is for
>>track projects including Project number, date, number of guests, estimated cost and amount charged
>>track employees and their personal information including social security number, address, phone number and name
>>have all the different types of food/drink offered by the database (Hint! This need only be a table with a single field: food type and you can make it the primary key.)
>>track what food/drink, including quantity is for each project
>>track which employees work on each project and how many hours they work each day (for billing purposes)

 

[MyBoo]

If you have the tables created, you should have listed them.

Employee table is the one side of a one-to-many table. EmployeeID should probably be the primary key.

The project table is the one side of a one-to-many table. Project# should probably be the primary key. It should include EmployeeID as you indicate Employees work on projects and can be on more than one project. EmployeeID would need to be a foreign key in this table.

The Client table sounds like it needs to be a one-to-many using the ClientID as the primary key. This would be another foreign key in the Project table.

How are you tracking food/drink? Per project or what is currently available... such as inventory? Is the food/drink being ordered based on project? Is the food from a list where the client picks from this list or can it be any food/drink items not currently listed?

The food/drink table has a few unknown variables but I think Iwould look at creating a lookup table to list the available foods and create a one-to-many table for storing the food/drink items for each project (one item per record along with qty needed).

So, you have the ability to track projects via the Project table, assign one client per project.... Client-to-Project = one-to-many relationship. Clients can have more than one project.
Assign employees to projects...Employee-to-Project = one-to-many relationship. One or more employees can work one or more projects. My assumption here is that after identifying the project, you are tracking how many hours an employee put into that project.

The food/drink table would use the projectID as it's foreign key, providing a one-to-many relationship... project-to-food/drink.

From the above, you would be able to list all Clients, all employees and all projects... individually or combined. You could list all employess and what projects they are working on. All projects by client and all food/drink (type/qty) per any/all projects.