big problem with database design

[carvalha]

hi

one simple question please. I have three tables.

GROUP

id    area
1   Multimedia
4   Education

Employment

  id   foreign key(GROUP)     job
   1    1                   designer
   2    4                   professor
   3    1                   copy

and now suppose that i have this

SpecificProperties

id  foreign key (Employment)      properties
1   1                             type of contract
2   1                             number of jobs 
3   2                             duration contract (no type of contract)
4   2                             number of jobs

the designer and the professor in this case have the property number of jobs. So basically the question is, i need repeat the number of jobs for each profession? there is any way to avoid? or i can have to number of jobs 8 foreign keys to 8 different jobs for example ? one for designer, other for professor, other for copy, and so on? in this way the first number of jobs have in the foreign key 1 || 2 || 3

something like this:

**SpecificProperties**

    id  foreign key (Employment)      properties
    1   1                             type of contract
    2   1||2||3||4                    number of jobs 
    3   2                             duration contract (no type of contract)

thanks

But this also give me problems because i need to store some information in the type of contract, duration, etc

so the correct must be this?

SpecificProperties

id    type of contract      number of jobs     duration

i need some help. seriously, i can't find a solution for this in last two days.

thanks!!

[BitBlt]

It looks like you stopped one step short in your normalization. You appear to have a many-to-many situation between Employment and SpecificProperties. Create a resolution table between them to fix the many-to-many, and you should be fine.

[carvalha]

thanks. another doubt that i have please. In your opinion, it is best practice a table for id, username and hash and another for address, age, photo, job, or it is best a unique table for all stuff?

[BitBlt]

If you are just doing a logical model, then there's only one rule: NORMALIZE. Once you're happy with your logical design, then you can take all the physical requirements into account.

So, to answer your question as I think you meant it, there are arguments both ways. If it's a large multi-user app with lots of transaction volume, you have security concerns, traffic volume concerns, storage concerns, concurrency concerns, and all that stuff. If it's a small app with relatively low data volume and volatility, putting it all in one table is fine. Each of those subject areas have to be considered as part of your physical design.

Not real specific guidance, I know, but this should give you some things to think about as you progress.

[carvalha]

If you are just doing a logical model, then there's only one rule: NORMALIZE. Once you're happy with your logical design, then you can take all the physical requirements into account.

So, to answer your question as I think you meant it, there are arguments both ways. If it's a large multi-user app with lots of transaction volume, you have security concerns, traffic volume concerns, storage concerns, concurrency concerns, and all that stuff. If it's a small app with relatively low data volume and volatility, putting it all in one table is fine. Each of those subject areas have to be considered as part of your physical design.

Not real specific guidance, I know, but this should give you some things to think about as you progress.

the security is important in this project and is multi-user system. regarding the number of users, i don't have any idea yet, but maybe thousands in the Medium-term.

I think that is best a separate table :)

[BitBlt]

Agreed. Good luck on your project!