OT: planning out mysql structure

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I haven't been using MySQL for that long and typically it's just a one-table type of ordeal. Now, however, I have a project that requires experience I don't have just yet.

The script is a mini client upload management system where the admin uploads a file to a user's directory. That's pretty simple if there is one user.

I have multiple users in which I have to create multiple directories for and upload files into their directories. Each upload will put a new directory inside their user folder (which the dir will be named after the auto-increment ID of the row itself).

I am really confused at how to do this.

I have one table setup "storage" for all the users and their files but that can't be right. I wouldn't be able to auto-id every upload to each user specifically this way. I want every user to start with row 1, 2, 3, etc.

I guess that's pretty much the question. How would I go about doing this? I know this may seem confusing or vague but I'm just that new at this :)

Comment on OT: planning out mysql structure

Replies are listed 'Best First'.
Re: OT: planning out mysql structure by VSarkiss (Monsignor) on Aug 30, 2006 at 15:10 UTC
What you're asking for is called "data modeling". It's a fairly broad subject. You may want to just start with Data Modeling 101 and then come back with specific questions once you have an initial framework in mind. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply]
Re: OT: planning out mysql structure by talexb (Chancellor) on Aug 30, 2006 at 15:19 UTC
I'm implementing a web application like that right now, using CGI::Application. Here's the data structure I've set up (with tables capitalized): Users can have multiple Memberships in any of a number of Projects; Files are uploaded to a specific project by a specific user. There's more detail, like having some users be staff members, with the ability to create new projects and new users. Staff members can also be managers for a specific project, allowing them to add/remove/create project members. But like the first respondent said, this is really a data modelling question and not a Perl question. So good luck with that. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re: OT: planning out mysql structure by planetscape (Chancellor) on Aug 30, 2006 at 17:26 UTC
I heartily recommend Database Design for Mere Mortals, 2nd Ed. HTH, planetscape	[reply]