multiple claims to data

[peanutgallery83]

I'll try to briefly explain what I'm trying to accomplish, hopefully someone can tell me the best way to do it because I don't really know where to go with it.

I have a table "reflist" where user data is stored when a user is registered.

In this table there are two classifications of users: 'referees' and 'assignors'.

What I'm trying to do is that when the assignors log in, they need to be able to select which referees from the database they want to have the ability to assign to work soccer matches. The reason for selecting those they want is that this program is being used statewide, and there are over 1200 referees in the database, so each assignor needs to be able to specifically name those referees he/she wants to have on their list to assign.

The problem comes in that there may be multiple assignors who "claim" the same referee. This is expected, I just need to get the code right to allow this.

What I would like to do is create an additional field in the 'reflist' table called 'assignor_link'. In this field, it would store the user id of the assignor when he/she selects a referee. The problem is, with multiple assignors possibility selecting the same official, wouldn't it override the data stored in that table everytime an assignor selected the ref?

As an example:

There are 3 assignors: Bob (user id 3), John (id #7), Bill (id #14)

There is a referee named Jack, and all three assignors want him on their list, so all three click on his name on the master list. When his name is clicked, it should store the assignors id# in the 'reflist' column "assignor_link". So if all three assignors selected Jack, that column would hold the numbers 3,7,14.

The problem is, I don't know how to make it add numbers into the column individually like that without overwriting previous data stored in the field, and later on when selecting the list of refs to view for the individual assignor, it needs to contain a WHERE assignor_link = $user_id so then I would need to separate out the 3 numbers so it could recognize the users id in the numbers stored.

Make sense?

I hope.

Thanks for any help.

[matto]

hello,

this is a data modeling problem.

when modeling your database, one would ask "one assignor has multiple referees, AND one referee has multiple assignors" -> so you need an additional table.

table 1: person (assignors, referees) (this one you have already)
table 2: assignor2referee

assignor2referee holds pairs of (assignor-ID, referee-ID).

for example:

PERSON
ID, type, name
1, a, bob
2, a, jack
3, r, joe

ASSIGNOR2REFEREE
ID-assignor, ID_referee
1, 3
2, 3

--> all data you need can be stored without any entry overwriting another.

hope I understood it.

---

btw: never try to store multiple values in one database field! someday such hacks will result in a disaster. google for "entity-relationship", "database modeling" for more info on this.

[peanutgallery83]

Thank you very much. That was the direction I had started to go, but then thought maybe there was a better way - hence the question.

Thanks again.

Landis