CODEWITHSUNDEEP
databasedatabase-design
katie hudson
katie hudson

Reputation: 2893

Database design - Managing documents

I asked a more generic question yesterday and done some work based on this. This question is more to the point in regards to something I am trying to work out.

So my application has a departments table to handle departments. So I am able to make different departments within my application such as Marketing and Finance.

The problem is, I know what departments I need to make, and these will be created beforehand (but I have made it like a CMS so an admin can edit departments etc). With the departments created, I envision something like this enter image description here

So a user can choose the department from a dropdown (remember, this is after departments are created). When they do this, the document dropdown should populate.

This is my problem, how can I associate specific documents to a department? Each document requires different inputs, so I would imagine they need to be different tables? At the moment I have enter image description here

But this doesnt really solve my problem whereby I can state that the Marketing department has a Brief document and Overview document.

How could I go about doing this seeing that I do not specifically have a table for each department? Would I need to create one for each department?

Thanks

Upvotes: 1

Views: 57

Answers (1)

Nir-Z
Nir-Z

Reputation: 869

You can do the following:

If the variety of the document's information you need to store is to large create a table which has 5 columns:

id,department_id,crated_at, updated_at, property

so for each property you will have a record in the table e.g:

id   department_id      crated_at        updated_at      property
1         454           2015-08-20       2015-08-22      x:34
2         454           2015-08-26       2015-08-26      z:234
3         934           2015-08-25       2015-08-26      y:45

This way you won't need table for each document type

EDIT: another option is adding one column for property name and one for it's value

id,department_id,crated_at, updated_at, property_name, property_value

Upvotes: 1

Related Questions