CODEWITHSUNDEEP
databasedatabase-design
katie hudson
katie hudson

Reputation: 2893

Database design guidelines

I have managed to get my database somewhat designed. So far, it looks like the following enter image description here

This is the scenario. The focal point is my projects table. A project belongs to a single client. So I might create a project for say SO. This project can have many users (image shows wrong relationship) that work on the project. Each of these users belongs to a department. So me and Bob may be working on a project for SO, I belong to Marketing and Bob belongs to Finance.

Here is where I am getting confused. Each department has a set of documents it needs to complete for a project. Marketing might need to do a Document a and Document B, and finance might need to do a Document C.

Say I do something like this enter image description here

I am basically saying that a department has one or more documents. But then you can see it gets messy. How can I show that Marketing has documentA and documentB and Finance has documentThree?

Would I need to define marketing and finance as a one to one relationship with the departments table, and then do documents coming from this? That would produce something along the lines of this

enter image description here

Is that the correct approach to be taking? Essentially, I need to link different documents to different departments, what would be the best way to achieve this?

Thanks

Upvotes: 0

Views: 605

Answers (1)

xQbert
xQbert

Reputation: 35323

Personally I don't like either approach; as they create separate tables for each doc type when the data in the tables is nearly typed the same. I would be more in favor of one table in these cases.

I would have a documents table with a type in it which indicates document type and is a forein key to DocumentType table which lists all document types.

Depending on how document types are chosen for project and department, I would have a DepartmentDocuments table which list the documents which each department COULD use. I would also have a ProjectDepartmentDocuments table which indicates the mandated and optional documents used for a particular project, along with their information specific to the department/project.

I also question the relationship from users to projects. It appears a project can only have one user. If that's the case your design is fine. However if a project can have many users, you should have a projectUsers table to handle many-to-many relationships.

Upvotes: 2

Related Questions