Solution Needed for grouping records

condoug

Hi all

I have a requirement where out of 1000 records for example, a user via the CMS can select one record and indicate which other records, from the 1000, are associated to it (i.e. like a partnership). This would be done via a multi select list.

Now, I need to understand what would be the best approach to storing this relationship data into a table and maybe what that table stucture would look like?

A typical scenario would be as follows: If someone associates Record 1 with Records, 23, 26, 38 and 123 (for example), and I go back into the CMS at a later date and view Record 26, I need to be able to retrieve (in my list box) the associated records for that Record 26 which would be the same as Record 1.

Can anyone help with the structure of a table that would store this data?

Please bear in mind, that Record 26 would automaticaly have to inherit Record 1 as Record 1 assocaited itself with Record 26.

I hope that makes some kind of sense!

Cheers
Doug

johanafm

Create a table to hold the relations.

CREATE TABLE record (
	id INT UNSIGNED AUTO_INCREMENT,
	PRIMARY KEY (id)
);
CREATE TABLE partner (
	from INT UNSIGNED,
	to INT UNSIGNED,
 PRIMARY KEY(from, to),
 FOREIGN KEY (from) REFERENCES record(id),
 FOREIGN KEY (to) REFERENCES record(id)
);

Retrieving all partners for record 1

SELECT id AS person, record.id as partner
FROM record
LEFT JOIN partner ON record.id = partner.from
WHERE record.id = 1

Now, this approach assumes that each relation is one way, meaning that x has a relation to y does not automatically imply that y has a relation to x, and it can be a good thing, but it depends on your needs.
You'd need to store two records in relation for a bidirectional relation:

from	to
1	23
23	1

If x -> y always means y -> x, then you'd have to somehow either only store the relationship once in the table, or always store both as in the above example. One way of dealing with a "one record biderectional relation" would be to always keep the lower id in from and the higher in to.
Inserts get easier to deal with, retrieval somewhat more complex.

condoug

Thank you for the reponse johanafm, it seems very understandable.

I'll put it in place and see how it goes.

My other thought would be to insert the same random id against each inserted recordid which would effectively keep them grouped. The only issue is that when I come to add another record to the existing group in future, I would have to obtain the groupid before doing an insert and I don't know if that's a bit overkill and a simpler solution would be out there somwhere.

Many Thanks
Doug

johanafm

Oh, I was a bit hasty and completely misunderstood you. Keyword: "group".

So ditch the above approach and replace the relation table with this group table. Should be pretty self evident.

CREATE TABLE group (
	group INT UNSIGNED,
	record INT UNSIGNED,
 PRIMARY KEY(group, record),
 FOREIGN KEY (record) REFERENCES record(id)
);

And then something along the lines of

FROM record AS a
INNER JOIN record as relation ON a.group = relation.group AND relation.record != a.record