Simple MySQL Logic

[jadedchron]

Hi all,

 

My girlfriend always says when we argue that I don't understand logic, after trying to setup a simple db structure, I'm beginning to think she's right 

 

I have two tables: one is Users with id, name and the other is Projects with id, name, user_id.

 

There are multiple users and each user can be assigned to multiple projects. My question is what's the best way to go about linking these two? I understand JOIN and it's straight forward, but my concern is won't I end up with a bunch of the same names in the table w/just different ID's or is that just how it goes?

 

See image to see what I mean:

 

I wasn't exactly sure what to search, fyi.

 

Thanks,

-Nick

[Mchl]

It's the pattern known as 'many-to-many' relation

You need third table, that will join these two

 

Like

table users_to_projects

userID, projectID

1,      1

1,      2

1,      3

2,      1

3,      1

3,      3

 

In table above, user with userID=1 is assigned to projects 1,2 and 3. User with userID=2 - to project 1, and user userID=3 to projects 1 and 3.

[cags]

I understand JOIN and it's straight forward, but my concern is won't I end up with a bunch of the same names

 

Apparently you don't

 

Table `projects`

project_id, name, user_id

 

Table `users`

user_id, name

 

SELECT p.project_id, u.name AS user_name FROM projects p JOIN users u ON p.user_id=u.user_id

 

Will return a list of all projects. Each row of data returned will include project title and the name of the user.

 

Perhaps the confusion is coming from you needing a project to have multiple users? The fact you have several projects with the same name might suggest this, but you didn't seem to stipulate that in your description.

 

Faux Edit: Mchl replied at the same time as me describing what to do if thats the case:

 

[jadedchron]

Aha. Many-to-Many. Sorry, I thought I had noted that users can be assigned to multiple projects while projects can have multiple users. The book I'm reading mentions to avoid m-to-m relationships in my design, but to instead use "intermediary tables that break down one many-to-many relationship into two one-to-many relationships." I assume that's what the users_to_projects is which is shown above.

 

That being said, if I have a table of `Tasks` and each project can have multiple tasks, would I still need to create a tasks_to_projects or simply insert a task_id in the projects table and use a JOIN that way?

 

E.g. Jane is a designer, John is a developer. They both are working on the same project `Google V2` and their respective tasks for the project is `design` and `development`. So, the project has multiple users and multiple tasks assigned to it. Does this make sense?

 

Thanks for your time,

Nick

[Mchl]

Aha. Many-to-Many. Sorry, I thought I had noted that users can be assigned to multiple projects while projects can have multiple users. The book I'm reading mentions to avoid m-to-m relationships in my design, but to instead use "intermediary tables that break down one many-to-many relationship into two one-to-many relationships." I assume that's what the users_to_projects is which is shown above.

Errr... yes... that's still m-to-n relationship. Just done right

 

That being said, if I have a table of `Tasks` and each project can have multiple tasks, would I still need to create a tasks_to_projects or simply insert a task_id in the projects table and use a JOIN that way?

 

You must answer for yourself:

- whether one task can be assigned to more than one project? (unlikely - so that's probably 1-to-m relationship)

- whether one task can be assigned to more than one person? (I would assume so - m-to-n relationship and another intermediary table)

 

P.S.

 

Are you actually working on Google 2.0?

[jadedchron]

Hi Mchl,

 

You must answer for yourself:

- whether one task can be assigned to more than one project? (unlikely - so that's probably 1-to-m relationship)

Yes, each project can have multiple tasks while other projects can have the same tasks, etc.

- whether one task can be assigned to more than one person? (I would assume so - m-to-n relationship and another intermediary table)

Well, a user can do multiple tasks and a project can have multiple tasks with multiple users assigned to that project. I don't think a task would necessarily be "assigned" to a person in general.

 

E.g.

 

Total tasks available (Project Management, Design, Development)

 

Project 1 Name: Google

Project 1 Users: Jane, John

Jane is designing for Google (that is her task), John is doing the development (this is his task)

 

Project 2 Name: Microsoft

Project 2 Users: John

John is developing IE6 to have even more bugs (his task is development).

 

Both projects have a development task on them, John works on both.

 

Would I need to setup a tasks_to_project for this scenario?

 

Sorry for my confusion and I greatly appreciate your knowledge/time.

 

Thanks,

-Nick

 

P.s I have MySQL Workbench to illustrate table connections, if that helps.

 

[Mchl]

If I were doing such a thing, I would assume that no single task can be assigned to two or more different projects. Instead I would create two 'development' tasks, one for each project.

 

Think of it: even though they have same name, the are not same tasks. Development task for Project 2 is introducing new bugs, while development task for project 1 is... whatever it is.