Datbase design (1 Viewer)

[iain1978]

Hi,

I'm really want to discuss datbase design. I am creating a new database and I need help with the basic design. I'm trying to creat a databse that profiles the access people need to do a specfic function.

Basically in my head the DB would be something like

Table 1 - Function Details --> Table 2 - Applications --> Table 3 - Access to said applications required to peerform function.

The trouble I have is I dont know how to connect the three tables together to make meanfull reports. In an ideal world I want to create reports that I can details something like the below on reports, but diffrent functions need diffrent access levels and diffrent applications so I'm a bit stumped.

Profile1
Function A --> Application 1 ---> Access level 1
Application 2 --> Access level 2
Application 124 --> Access Level 25
Application 2001 --> Access Level 30
Profile2
Function B --> Application 1 ---> Access level 5
Application 2 --> Access level 42
Application 22 --> Access Level 7
Application 209 --> Access Level 10

I just cant get my head round the joining of all thre tables I can easilly join table one to table two togeher but then how do I join the third strand of information required to create meanfull reports.

I know I can create Junction tables but there are hundreds of applications and hundreds of access levels and dont really want hundreds of junction tables as it would look a bit messy.

Any ideas or comments please, and hopefully I have explained myself clearly.

Thanks

 

[Rabbie]

You would join/link Table 3 to Table 2 in the same way you would link Table 2 to Table 1.

The best way for your reports would be first to build a query that collects the data from your tables then build your report on this query.

The query would look something like

Select * from Table1,Table2, Table3 where Table1.ID = Table2.Tab1ID and Table2.ID = Table3.Tab2ID

Hope this helps

 

[iain1978]

Sounds simple when you put it like that.

And actually it was

Thanks

 

[Rabbie]

Glad to be of help.