need feedback on db design

[drtanz]

Hi I have made a tentative db design for my system and would like some feedback on whether you think it is well constructed, it can be found here:

www.jpgalea.com/design.jpg

Thanks

[debasisdas]

thats OK ,but u have mentiond only PK ,what about foreign keys .
you have not mentioned how are you maintaining ralations.

[drtanz]

thats OK ,but u have mentiond only PK ,what about foreign keys .
you have not mentioned how are you maintaining ralations.

in MODULES, FK is review_id

in MODULES_ROLES_USERS, FK are module_id, user_id and role_id

in MODULES_TASKS_USERS, FK are task_id, module_id and user_id

in ROLES_TASKS, FK are role_id, task_id

makes sense?

[debasisdas]

it seems to be a good design following norms, but all depends on what is the logic you are going to implement on that. How youare going to use that is more important , on which i have no idea. It might happen you have to make some minor changes later on.

[drtanz]

it seems to be a good design following norms, but all depends on what is the logic you are going to implement on that. How youare going to use that is more important , on which i have no idea. It might happen you have to make some minor changes later on.

I have uploaded a document with the description of the system. Someone has also remarked to me that I should split the modules_roles_users table into three: modules_roles, modules_users and roles_users but im not sure if this is better, what do you think?

[debasisdas]

As per rules of normalization ,that sounds good but in most of the cases we have to de-normalize the database to avaoid un-necessary complexcity. You can go with the structure and always there is room for improvement. Different people have different openion on the same scenario, and this is my personal view only.

[drtanz]

As per rules of normalization ,that sounds good but in most of the cases we have to de-normalize the database to avaoid un-necessary complexcity. You can go with the structure and always there is room for improvement. Different people have different openion on the same scenario, and this is my personal view only.

So you think i should be able to work with it even without splitting those tables?

[drtanz]

also i am unsure about using a separate id field for the primary key in the join tables rather than using the two foreign keys together as a primary key. what is ur opinion on this? thanks

[debasisdas]

no need to split the tables further, it might look efective to you initially but later on will create more problems than it will solve and will be difficult to manage as the tables grows.

[debasisdas]

also i am unsure about using a separate id field for the primary key in the join tables rather than using the two foreign keys together as a primary key. what is ur opinion on this? thanks

Even though u can use composit keys ,it is always recommended to use Primay key and again composit key based on foreignkey is not that recommended for any purpose. That should be used as the last option.