CODEWITHSUNDEEP
mysqldatabase-design
user1949366
user1949366

Reputation: 465

Common methods for associating various tables with vast amounts of data

I am creating a large project management system for a client. This involves many users working with many different projects. I am struggling to come up with a method of associating different tables on mySQL.

For example, i have several tables; users, projects .ect. Each user must be able to have essentially an unlimited number of projects associated with them, and visa-versa, each project must be able to have an unlimited number of users be associated with it.

My question is, How do companies with huge amounts of data associate such data between tables?

I could just have a huge amount of columns in users, with each project id, or the same in projects with user id. But then how do I then efficiently, get each project/user id field and then query the other table to retrieve its data?

The other option is to have an individual table for each project or user, but I only have one database to play with and i dont want it to be swamped by users or project tables.

What is the common method for this. Because many people must face this, and I am clearly missing something. Thanks.

Upvotes: 1

Views: 27

Answers (1)

Benny Hill
Benny Hill

Reputation: 6240

What you have is a Many-to-Many relationship between users and projects so you need a "Joining table" like this:

projects
    id              unsigned int(P)
    name            varchar(50)
    ...

users
    id              unsigned int(P)
    username        varchar(32)
    password        varbinary(255)
    ...

users_projects
    id              unsigned int(P)
    user_id         unsigned int(F users.id)
    project_id      unsigned int(F projects.id)

Upvotes: 3

Related Questions