inserts into a many to many database.

[Anon]

hello. i'm at the beginning stages of creating a many-to-many mySQL database using PHP. it has quite a bit of tables, a lot of unique ids and such.

i haven't ran into any problems yet, other than when inserting new entries, i'm stumped on a logical way of doing this.

what's the best way to keep track of the ids when inserting? for instance, say i had the following tables:

userid username
1 bob
2 jane
3 joe

rel_userid rel_companyid
1 1
2 1
3 2

companyid companyname
1 abc, inc
2 123 enterprises

and say i wanted to insert 'john', who works for '123 enterprises'.

1. i would need to check to see if the company exists, then if it doesn't we would add it (and also create the new unique ID using auto_increment).

2. i would have to check to see if john exists, if not, then we would have to add him along with a unique id for him.

3. then we would have to plug those two unique ids(if applicable) into the relation table.

how would i go about doing this? is there some logical way of doing this, that i'm over looking? i know that i could rig some way for php to do it, but i figure if there's a way to do most of it in an SQL statement, it would be faster.

any ideas or suggestions are welcome,
thanks in advanced. -- jp

[[deleted]]

Well, there are a few logal rules that apply.

Most importantly, you can't link what's not there.

So John cannot work for '123 enterprises' unless '123 enterprises' exists.

That means your web-interface can never offer you the choice of linking John to 123, because there is no 123.

The same applies for John himself, you can't link him if he's no there.

So you don't have to check wether John or 123 exist, because if they don't, there's no way to get John to work for 123 in the first place.

If you're loading the data from a textfile or something, the 'obviously' you'd have to load the companies first, then the people, and then build the relations between them.

PS: make sure you either use MySQL with InnoDB, or no MySQL at all. MySQL without InnoDB does not support PK/FK constraints, which means you can link John to 123, and then remove 123 without problems, which is ofcourse extremely unbelievably bad.

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

But of course you 'obviously' need to check to see if 123 exists so you don't add a second entry for that company. You don't need to check if john exists because there will always be more than one john in the world. Besides you're not using john for the relation, just the numbers and since you're assigning the numbers to john you can use mysql's auto_inc as uniques.

I'm doing something very similar to you, as long as you're a careful programmer you don't need InnoDB. From the way I've heard InnoDB described its for lazy people who can't think in more than 2 dimensions while coding.

Currently I take a list of "normal" people, associated with "higher" people, assign them a GUID (globally unique id, which is the auto_inc you described), then I add each "normal" person with their own GUID, placing the "higher" GUID in another column or the "normal" person.

This means no relational database needed. Any time I want to see every "normal" associated with a "higher" I just "select * from normals where higherguid='X';" Then I have the same result as your relational database with 2/3 the effort.

Now we have complicated regional data that gets entered at the same time, about 23 extra columns. This allows us to do regional relations accross many "highers" and "normals" independantly to compare their results with others.

Each time a new set of "normal" and "higher" users get entered the insert script will make a quick scan to detect irregularities in the relations (ie. "normals" without "highers" and "highers" without "normals") and e-mail me with any errors.

Also the scripts that output this data also do a check for the same irregularities.