Database Design

Hello,

I'm designing an application that will be supported by a DB.

I have two kinds of "objects",: "Tasks" and "Persons".

Each "Task" has at least one "Person" but it may have up to four "Persons" (there's at least one person taking care of it but that person may have an assistant, and at night there may be another 1 or 2 persons). A given person may be working on more than one task.

So basicly I'll need the following features on the application:

- List of all "Tasks"
- List of all "Persons"
- Detail of all tasks (where it shows the Persons that work on it)
- Detail of all persons (where it shows the tasks they are working on and what they do main/assistant, day/night)

I've been working on these tables and this is what I have now:

TASK
-> id
-> title
-> date
-> description
-> main_person_id
-> secundary_person_id
-> main_night_person_id
-> secundary_nigh_person_id

PERSON
-> id
-> name
-> email
-> phone

Then I'll have a join connection 4 times the TASK table with the PERSON table, getting the info to create the task detail page. Same principle applies to the detailed information of each PERSON.

But I was thinking about creating three tables:

TASKS
PERSON
TASKS_PERSON

Where the TASKS_PERSON table would relate the two others. Something like this:

TASKS
-> id
-> title
-> date
-> description

PERSON
-> id
-> name
-> email
-> phone

TASKS_PERSON
-> person_id
-> main TRUE FALSE
-> secundary TRUE FALSE
-> day TRUE TALSE
-> night TRUE FALSE

I need explicit DAY/NIGHT fields because I don't know if the PERSON is only DAY ( it may be both). Otherwise I would just use a FALSE day as indication of NIGHT. The same applies to MAIN and SEC.


Which one do you think is best for the problem at hand (and why). Do you recommend some other model?

Thanks in advance.

TASKS_PERSON needs task_id

otherwise it looks good


rudy
http://r937.com/

You're using two boolean fields to represent three possibilities (day, night, day and night).

I would combine them into a single integer column ( called 'shift'?) that would be a key into a definition table that has all the possibilites for the 'shift' column defined.

This way, if a third shift is ever created, you can just add a new row to the 'shift' table and start using its id. Otherwise, you would have to add columns to your tables.

I would also consider something similar for the 'main' and 'secondary' columns in that table.

Do you guys use any software to help you design your DB's?

I'm trying to design a DB with 1:M and M:N relationships but it's been quite some time since I've done anything of the kind and I can't seem to find my way. Any suggestions? Anybody care to help someone relearn?

I'm playing with Case Studio 2 but I'm still quite confused in terms of entity relationships.

a long time ago, i used to use a rubber stamp
(see http://r937.com/20020514.html)

i have used ERwin and ER/studio, but unless you are designing a corporate-sized database and need to keep track of hundreds (i'm not kidding) of tables, paper and pencil is fine

after all, it's really just the entities and their primary and foreign keys that you need to keep track of, the attributes that belong to each entity are trivial

rudy

Thanks for the link r937,

Problem is that once I've designed the DB I'll need the SQL to build the DB. As I'm using MYSQL as my DB platform I won't know exactly what to do with Primary Keys, Foreign Keys and Composite Keys.

This is why I've opted to use a DB designer. DB's are obviously not my strongpoint and I know that what I'm trying to do isn't challenging to someone with any bit of expertise in this field but, that's why I'm looking for the simplest route.

check out these tools --

http://www.databaseanswers.com/modelling_tools.htm


rudy

I"m already playing with the second from the top Case Studio. So far so good. I'll see if I should try any others. Thanks for your help so far r937.

is it wrong that one of my tables contains only keys from other tables to bring them all together?

wrong? not at all, that's quite typical of a many-to-many "relationship" or "junction" or "association" table

great! that's a good sign then! lol I"m quite nervous about this.
Now there seems to be something wrong with this statement and I can't figure out what. I do receive a "warning" in Case Studio 2 when it creates this SQL statement.

Warnings - Column name "desc" in entity "scene" is a key word.

What does this mean? What's the problem?



Btw r937 I really appreciate all your help. You have no idea.

you're getting that error message because "desc" is a reserved word

try descr instead

I didn't see that coming. I'll try it.


Yeah that worked!

In this M:N relationship scheme that I've got all the keys are in the table as Foreign Keys. Is this incorrect? Because I want to add more than one entry with some duplicated foreign keys but it's not allowing me to do so.