Help in designing simple 2 table database?

Question

Trying to create a simple 2 table database that lists the contents of various firewire drives, one table with FW number, the other with a file item on that drive number.

eg. fw 233 file -> mike's home movie.mov

I'm very new and can create single tables with queries, but haven't understood querying multiple tables with join.

each drive will have many files, and the point of this task is to learn, but really to have a living list of items on these drives, one that I can update frequently.

Any help would be great...

I'm using codeigniter and of course PHP

Answer 1

You need to have a primary key on your Drive table and a foreign key on your File table.

Drive Table
-----------
DriveID
FWNumber

File Table
----------
DriveID
Filename

To see all of the files on DriveID 1 you would...

select Filename 
from File
where DriveID = 1
order by Filename