Database design guidance

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 8188 Views - Last Post: 30 August 2015 - 02:24 PM Rate Topic: -----

#1 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Database design guidance

Posted 28 August 2015 - 03:20 PM

Hi guys. I have got a bit stuck designing my database. At the moment I have something like this
Posted Image

So a Project has a user and a client. A user is part of a department. This is where I am stuck. I essentially have a lot of CRUD going on. You can create a Client, Department and User. When you create a User, you assign it to a department. Now I want to deal with the projects documents. So I envision going to a page like the following
Posted Image

There, you select your department and document. The document should then appear as form inputs. Although I allow departments to be made, I know what them departments will be. Each department also has a set number of documents it needs to create for a project. So Marketing might have the documents Brief 1, Brief 2 and Brief 3. All three documents will be unique, and require different inputs.

Now because each department has different documents, should I make a database table for each department rather than a generic departments table? I know the answer to this is probably no, but then how can I link documents to a department?

I assume because every document is unique, I will need to do a new table for each document?

Really, any guidance as to what I am attempting to do would be fantastic. I am just unsure how I can link these departments and the unique documents for these departments to each other.

Many thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Database design guidance

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,398
  • Joined: 03-December 12

Re: Database design guidance

Posted 28 August 2015 - 03:55 PM

A document should be linked to a project. You could also have a column that links it to a dept, or you could specify a type that would do the same thing when queried.

documents:
id,
project_id,
type,
version,
location,
upload_timestamp,
title

This post has been edited by astonecipher: 28 August 2015 - 03:56 PM

Was This Post Helpful? 1
  • +
  • -

#3 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 02:42 AM

The problem is that a document is not uploaded. The documents will be generated by my system, and each department has different documents. Does that mean I need a table for each document as they all require different inputs?
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2343
  • View blog
  • Posts: 9,398
  • Joined: 03-December 12

Re: Database design guidance

Posted 29 August 2015 - 09:48 AM

Depends how your system creates the document, but all of the documents should be in a single table.you may have a table that can take the parameters to built that for though, but I would say a template file would serve you best.
Was This Post Helpful? 0
  • +
  • -

#5 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 10:27 AM

I am just going to use some library to create the documents, but I need all the data saved in the database. So say I have documentA, which expects:

Client Name
Project Assets
Project Leader

And then I have documentB which expects:

Project Cost
Project Start Date
Project End Date
Project Deliverables
Project Signer

How can I represent these two different documents? Would I need to handle them individually e.g. have a documentA table and a documentB table?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 12:11 PM

Presumably when a document is produced it is based largely on information that is already in the database?

In which case, each document could have a corresponding query that collects the information it needs from the database, based on the Project (or User or Client) that the person printing the document selects from some drop-down lists.

In terms of recording the sending of a document, you would need a table to store the document code/id, the ProjectID, etc., and the date it was sent. There may be additional, variable, information that can be requested (such as Greeting) that could also be stored alongside these details, or in a linked table (if they are even needed to stored). Storing details of the document's code, the ProjectID, etc., and date sent, is all that you need (hopefully) as all the other information is already in the database. It will be easy then to produce a Project report later, listing all the documents sent, and in date order.

This post has been edited by andrewsw: 29 August 2015 - 12:12 PM

Was This Post Helpful? 1
  • +
  • -

#7 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 12:13 PM

Actually, most of the data would not have been collected yet, it is collected when the document type is selected as in my original post. This is whats confusing me, because each document is different, and I need to collect the data for each document, I surely have to create a new document table for each document? And then how do I link the documents to their associated departments?

Thanks
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 12:29 PM

If the documents are created based on data that doesn't exist in the database then, yes, you'll probably need a table for each document to store the details that are needed before producing the document. (That is, assuming that you need to store these details for later reference?) Presumably, and as you've indicated, the documents are too different to consider creating a single table with many columns to account for the variety of information that may be needed for any particular document?

An alternative, but probably not recommended, approach, is to have two tables. The second table stores the DocID and DocFields, and these DocFields are used to generate rows in the first table to store the information for each necessary field. Well, it may not be recommended, but is something that I would probably consider.

Quote

And then how do I link the documents to their associated departments?

You store the DeptID alongside the DocID (and DateSent, etc.) in a table.

This post has been edited by andrewsw: 29 August 2015 - 12:32 PM

Was This Post Helpful? 1
  • +
  • -

#9 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 12:40 PM

View Postandrewsw, on 29 August 2015 - 07:29 PM, said:

An alternative, but probably not recommended, approach, is to have two tables. The second table stores the DocID and DocFields, and these DocFields are used to generate rows in the first table to store the information for each necessary field. Well, it may not be recommended, but is something that I would probably consider.

Actually, I would be happy to do this, as you won't be transposing field-values into field-names (I'm assuming). It is still a bit of a challenge though, to correspond values in textboxes with specific fields for a particular document.

This post has been edited by andrewsw: 29 August 2015 - 12:43 PM

Was This Post Helpful? 0
  • +
  • -

#10 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 12:44 PM

Thanks for the information, makes things clearer. One last thing I wanted to ask because I can't find anything about it online. I am going to create a table for each document because I think it is easier for me to handle this way. However, the project will have no constraint as to whether it will use the document and if it does, it can only use it once. So in essence, its a none to one relationship.

Is there such a thing?
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 12:55 PM

No such thing ;). For a table that contains a ProjectID (you've switched from departments to projects?) and DocID then a unique index can be defined for this combination of keys, so that the same document cannot be produced more than once, for the same project. If the document needs to be reproduced for any reason then you would have to delete the previous record first.. losing this historic information.

[This effectively creates a 1 to 1 relationship (or 1 to null). It is not a true 1-1 because the ProjectID can occur more than once in the other table.]

Think broadly though: will there ever be a reason why the same document might need to be produced more than once for the same project? If this could be the case then it could be considered adding an additional field storing, say, Repeats as an integer, or just a boolean/bit field, indicating whether a doc can be sent more than once.

This post has been edited by andrewsw: 29 August 2015 - 01:00 PM

Was This Post Helpful? 1
  • +
  • -

#12 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 01:17 PM

Brilliant. Just to make sure I am on the correct sort of track, I have produced the following.

Posted Image

I have removed my other tables for now, just want to concentrate on this. So a project can have one to many documents. And then in the documents table, I list each different documents id, so they can be linked to an individual document table as a one to one relationship. The image shows the individual document tables to have the same data, this wont be the case when I do it properly though.

Is this the sort of thing I should be doing? I have a feeling that projects to documents should be one to one?
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 01:32 PM

You should not be repeating the document id's as columns in the documents table, why would they occur twice, for the same project?

Nor should you have document1id and document2id.. what happens for document3, document4? Numbered fields 1,2, etc., are a strong indicator that the design should be reconsidered.

Quote

ProjectDocuments
-----
ID
ProjectID
DocumentID
DateSent

This post has been edited by andrewsw: 29 August 2015 - 01:35 PM

Was This Post Helpful? 1
  • +
  • -

#14 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,903
  • Joined: 12-December 12

Re: Database design guidance

Posted 29 August 2015 - 01:40 PM

Also, your design doesn't yet allow for the storage of the information which will "not have been collected yet".
Was This Post Helpful? 0
  • +
  • -

#15 nena-p  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 22-May 13

Re: Database design guidance

Posted 29 August 2015 - 01:53 PM

Sorry if I am a bit slow, still wrapping my head around these relationships. So I have now come up with this

Posted Image

So a project can have one to many project documents. The project_documents table can then have a one to one relationship with all the individual documents. These tables will be have fields to house the data which has not yet been collected (will be via forms), hence allowing me to now store this information.

In theory it sounds ok, am I on the right tracks now?

Thanks
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2