CODEWITHSUNDEEP
sqldatabase-designdata-structures
Benjamin Delaney Ellingson
Benjamin Delaney Ellingson

Reputation: 13

Best Way To Design Database to Accommodate Indeterminate Number of Fields Per User?

I'm trying to understand how to design a database that can accommodate an indeterminate number of fields per User.

For example, User1 is involved in Projects A, B and C. User2 is involved in Projects A, C, E and G while User3 is involved in Projects A, B, C, D, E, F and G.

A table with Users listed in rows and then 7 more columns to record the Projects they're involved in would serve quite nicely... until I have to accommodate User4 who winds up being involved in 100 projects, which, in my model, would require 100 columns for keeping track.

There would ultimately be no way of knowing in advance how many columns to define.

I could flip it around and associate User1 & User3 with Project B, but then further on down the road, after there are 10,000 Projects, I'd have to look through each Project record to produce a complete list of Projects that a given User has been involved in, which seems terribly inefficient.

So... how to keep track? Just can't quite wrap my head around this one.

Am total newbie, so forgive me if this is poorly stated or grossly elementary.

Thanks,

Ben

Upvotes: 0

Views: 256

Answers (2)

thekaveman
thekaveman

Reputation: 4399

It sounds like you may be trying to mix these two entities (user and project) into a single table, which really isn't the best way to go.

A different approach is to have your user info in a user table, project info in a project table, and another "link" table like user_projects, which would just contain the ID fields from the user and project tables, one entry for each user in each project.

an example schema:

user (userID, firstName, lastName)

project (projectID, projectTitle)

user_projects (userID, projectID)

To select project titles for user 3, you could do something like:

SELECT p.projectTitle 
FROM project p INNER JOIN user_projects up ON p.projectID = up.projectID
WHERE up.userID = 3

The point of the user_projects table is to link the information contained in the user and project tables, that is all.

Upvotes: 4

kirilloid
kirilloid

Reputation: 14304

Typical many-to-many relation.

You need to crate a table, where each entry would be a relation: user_id, project_id. If some user (id=uid) is involved with project (id=pid), then and only then there's a pair (record with 2 columns) in that table: user_id, project_id.

Yes, this may sound strange to person never seen this, but this is how it's made everywhere and DBs are ok with processing speed of such tables.

If later you decided, every user should have some role in project, you'll be able to add other fields to that table.

Upvotes: 3

Related Questions