[SOLVED] mySQL db design advice?

[biggieuk]

Hi all,

 

I am creating an online application where the user can login and upload audio, images and video to various servers.

 

I have created a login table consisting of username, password.

 

I am unsure of the best approach of storing the addresses for the uploaded media. Would it be best to dynamically create a table named by username that lists all of the uploaded media for that user?

 

I need to do this so that when the user logs in to my application, their uploaded media can be displayed in order of audio, vid, and images.

 

Any ideas or advice is appreciated!

 

Dan.

[dbo]

User

------------

id

username

password

 

 

MediaType

--------------

id

name

 

 

Media

-------------

id

user_id

mediatype_id

data

 

You certainly don't want a table for every user.

[biggieuk]

Thanks for the reply. I have a few questions,

 

Would i need 'id' if the username for each user will be unique?

 

I am unsure of the data that will be stored in the Media table. Could you give examples of data that may be stored in these?

 

e.g.

 

id  |    user_id  |  mediatype_id  |  data

-------------------------------------------

?  |        ?      |          ?          |    ?

 

 

 

 

I need to be able to run a query and find all of the media for that user.

 

Thanks again,

 

Dan

[dbo]

MediaType would just be a code table. This would have things like "Audio", "Movie", "Image" or whatever you think is appropriate. Think of it as a tag that goes with your content.

 

The Media table would be the meat of the datamodel....

 

Basically, id would be auto generated, user_id would be whatever you think is appropriate. I typically just use auto numbers but if you'd rather use username as primary key... go for it. MediaType_id would be a foreign key from your code table (see above), and Data would be the actual media file that was uploaded. You can either store the actually binary data in this field or a reference to where it is on your server... that is a choice you'll have to make.

 

Here's an example:

 

user

---------------

1, derek, ad23tgasd3gdafaadkj;;adfasdg

 

mediatype

-----------------

1, Audio

 

media

-----------------

1, 1, 1, ./files/derek_audio.mp3

 

 

[biggieuk]

Ah yes, i see now.

 

Thanks for your help dbo!