design question???

[bakaneko]

Okay, so the design is this. members submit information about a certain subnet. but we want them to be able to only look at their subnets and no one elses. the subnets are entered into a seperate table than the members table and have unique names.

so, how can we implement this so that. one, the user can only perform operations on his or her subnet. two, in the future if the user submits a new subnet this subnet is tied into his or her user account. and, three prevent other users from accessing the user subnet information?

right now i was just thinking about attaching it to the end of the member table. so i have 4 columns of member information and after a list of blank columns. and then i will use mysql queries to compare the information in the member table with the name of the subnet. is this the best way to do it?

i know lots of questions. but, i just want the input of some old timers and new too.

thanks,

bakaneko

[sarahk]

this design will allow each member to have several subnets and each subnet to be accessible by several members. It's then up to you to allow access as appropriate

subnet table
subnetid
subnet name
* other subnet info

member table
memberid
name
* other member info, dob, phone, fax etc

sn2member table
sn2memberid
subnetid
memberid
other info, date added, authorised by etc

So there is no record of the subnet on the member table. To check if access is allowed you need something like

$sql = 'select count(*) as cnter ' 
  . 'from `subnet`, `member`, `sn2member` '
  . "where subnet.name = '{$subnet}' "
  . 'and subnet.subnetid = sn2member.subnetid '
  . 'and sn2member.memberid = member.memberid';