User database with files for each user...

Better to create separate tables or one table?

[HughMungus]

Let's say you want to use a database to store user data where the data for each user consists of a username and a list of files that correspond to that user. Is it better to create one table with each user being a column or create a new table for each user?

TIA!

[pete_m]

It depends a lot on the data you want to store.
How many files are you talking about?
How many users?

The most flexible way would be to have one table with two columns: UserName and FileName

so if user Foo had two files: Bar1 and Bar2 it would be stored as

UserName FileName
-------- --------
Foo   Bar1
Foo   Bar2

the sql to retrieve Foo's filelist would be

select FileName from [tablename] where UserName='Foo'

[HughMungus]

Thousands of users, thousands of files. I'm asking because the files in question are audio files (.mp3 - not music) and I want to be able to start using hpyerlinks to the files that are not direct links.

[pete_m]

With thousands of users and files it's not practical to have one table per user, and impossible to have a table with thousands of columns.

The single table I described above is probably the best way of storing the relationships between users and files.

If you need to store more user information then JOIN the table above to a separate User table in the SQL query.

[Zaphod Beeblebrox]

Definitely make 2 tables. Storing the username in veery single record would take up way more space than needed.

Table User:
Id - Autonumber
Name - ...
Other fields

Table Files:
UserId - Number
Filename - ...

Then use "Select * FROM Files WHERE UserId=" & UserId

[HughMungus]

Great. Thanks for the guidance.