member > project DB design

[rondog]

I am creating a small networking site where a member logs in, they have access to an asset library which is no problem. I know how to make a members database and the asset library should be rather easy as well.

 

The part I am struggling with is each member will be allowed to create as many  'new projects' as they like where they build things using the asset library, save them and when the log back in a later time, they can load their projects.

 

My question is how could I setup a database where each member will have references to all of their projects.

 

Hope you guys can understand what I am trying to explain.

[rondog]

so an idea I came up with is each member has a field called 'projects' which is just a reference to other dynamically created tables. The dynamically created tables would be the new project.

 

So the projects field would be like something like:

 

project1,project2,project3,project4

 

Those names would obviously be table names and then I could just use the split method to get an array of all their projects. Each one of those tables would reference items from the asset library.

 

Any better suggestions??

[rondog]

If I do what I did above, what would the field type be for the projects? I am thinking just text.

[a-scripts.com]

Hey, I think the classic approach would be like this.

you need 2 tables:

 

1, mebers table containing all the columns for info you need ( id, login, pass, email ... )

that's pretty obvious

 

2, projects table containing the info about projects ( id, title ... ) plus containing the column with

referenced id of member who is the owner of the project.

 

Seems like you want to make a 1:N relationship between member and projects. This way you can load up all projects for a member like

 

SELECT * FROM projects WHERE projects.member_id = MEMBER_ID

 

this allows you to make some JOINs and fancy stuff like that . NEVER use something like project1, project2 and the spliting inside one table column !! It's pretty natural to use some kind of relationship when one Entitiy is related to other.

 

Project and Member are entities in your case. And every Member can have zero or more Projects so you just use the 1(member):N(projects) relationship.