Relational database question (how best to achieve this)

[JohnnyT]

Hi

I'm redesigning my company website and I've come across a problem that I'm hoping some of you will be able to help me with.

I want to have a "customer login" section of the site. When a customer logs in they can view three categories of files that pertain to that customer. They are Drawings, Manuals and Software. I will be uploading the drawings/manuals and software to the website and so I assume that the table will have to hold the 'link' to these uploads.

I need to design a database that will allow customers to access multiple files but only the files that pertain to that customer.

I am thinking that I'll need two tables, one of customers log in details and one that holds the file names of the relevant drawings, manuals and software that they are able to download.

The problem is that I want multiple customer log ins to be able to see the same files. Basically like a 'permissions' thing. I'm not sure on the best way to do this in a database and I wondered if anyone has any ideas?

If I've been a bit vague or you want more info then please don't hesitate to ask.

Many thanks for any light you can shed on this..

Cheers

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

This sounds to me like a many-to-many relationship, and classically, this is represented not by two tables, but by three. In your case, a table to record customers, a table to record files, and a third table to record the relationships between records in the other two tables.

Assume your customer table looks something like:

[tt]+------+---------------+
| pkID | name |
+------+---------------+
| 1 | Adam Aames |
| 2 | Ben Billson |
| 3 | Chuck Collins |
+------+---------------+[/tt]

And your filenames table looks something like:

[tt]+------+-----------------+
| pkID | filename |
+------+-----------------+
| 1 | IDphoto.png |
| 2 | spreadsheet.ods |
| 3 | audio.ogg |
+------+-----------------+[/tt]

Then if Adam Aames can access only audio.ogg, Ben Billson can access IDphoto.png and spreadsheet.ods, and Chuck Collins can access all three, a table cust2file might look something like this:

[tt]+--------+--------+
| custID | fileID |
+--------+--------+
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+--------+--------+[/tt]

Thus a query like:

select
c.name, f.filename
from
customers c, cust2file cf, files f
where
c.pkID = cf.custID and cf.fileID = f.pkID
order by
c.name, f.filename

will return:

[tt]+---------------+-----------------+
| name | filename |
+---------------+-----------------+
| Adam Aames | audio.ogg |
| Ben Billson | IDphoto.png |
| Ben Billson | spreadsheet.ods |
| Chuck Collins | audio.ogg |
| Chuck Collins | IDphoto.png |
| Chuck Collins | spreadsheet.ods |
+---------------+-----------------+[/tt]



Want the best answers? Ask the best questions! TANSTAAFL!

 

[JohnnyT]

Superb answer!

Thanks very much. I'll do it with the three tables as you suggest.

Much appreciated

JT ;-)

I don't make mistakes, I'm merely beta-testing life.