separating the Data Access Layer and Business Logic Layer.
I've designed the database.
As I may only be able to upload one database to my free hosting site,
so I have to share the same database for all my future projects.
There will be some tables sharing among these projects:
* User
* Attachment
The main tables for MyProjectManager are:
* Project
* ProjectTask
* ProjectMember
* TaskAssignee
* ProjectAttachment
* TaskAttachment
* ProjectTimeLog
The reference tables include:
* ProjectApplicationType (whether Web application or Windows application)
* ProjectStatus (Not Started, On-going, Completed, On-hold, Terminated)
* ProjectPhase
* TaskType (whether Project Task, Project Request or Project Bug)
I was confusing whether I should combine ProjectAttachment & TaskAttachment tables into one,
as they serve for the same purpose, except one for Project, another for Task.
Same go to ProjectMember and TaskAssignee.
Well, this is my first version of database design.
I really gotta get it up, and start work on it, otherwise it'll never end. ^_^
You may download the database diagram here:
(DatabaseDesign.zip : 19.4KB)
"should combine ProjectAttachment & TaskAttachment tables into one" - In my opinion, these 2 actually can combine with Attachment table as well. Why do you need to put many to many relationship to that table if you only have id, attachmentid and projectid? these all fields u can actually store in Attachment table as well, unless you have extra information in ProjectAttachment & TaskAttachment tables. The more denormalized table that you are having, will have more inner joins or joining in between of tables, it might decrease ur performance...
ReplyDeleteSecondly, these 2 tables should be combined into one if they are having same fields. What you can do is using a type to control, eg: sourcetype. why? one of the reason is maintaining, you wun need to maintain 2 tables if any changes made to any of these tables. And of coz, this will save some space of your database as well, since after you combining them, less field will need to be created.
"Same go to ProjectMember and TaskAssignee." - for this, i think its fine if u leave that to be 2 tables, but u need to make sure your fucntional spec of this, is logically to design in such a way. from ur db diagram that i see, i think still reasonable to do so... bare in mind that, ur db design has to come together with ur functional spec, these 2 things are very close to one another.
ReplyDeletehmmm.. a few comments and suggestions for your db design ya...
ReplyDelete1. ry to put DB relationship into your db diagram, this will show clearly to which table link to which table.
2. Put meaningfully ID's name to the table. Why? easy for you to refer back.
3. CreatedBy, CreatedDate, ModifiedBy, ModifiedDate for all tables - i dunnu whether these all fields are useful to you, but normally we will have these all field to know when the data was created, created by who, when data was modified, modified by who...
since i cant see you data field assigned to the table fields, so, i cant comment on ur db data type then.. 8>