Re: several pointers to records in one field

Le lundi 17 mars 2003, 16:12:32 ou environ CPT John W. Holmes <holmes072000@charter.net> a écrit: > > I started to use PHP with MySQL last december. > > Personal use: list of inhabitants of a town in the 15th century > > > > Problem is the following: one person has an arbitrary number of children. > > It is heavy to have fields child1,child2,child3,...,childn containing the > row > > number of each child especially if the number of children is variable. > > > > When I played with Commodore VIC20 I used to put pointers to records in a > > string in the form pointer1,pointer2,...,pointern in a string. > > > > I think of using explode and implode to put all the record numbers of the > > children in one varchar field. > > > > If someone have a better idea? > > NOOOOO!!! Don't do that... that's horrible database design. :) I agree and that's why I sent this e-mail. > > You should/could have a separate table for children. It'll have 3 fields > > 1: Unique ID for each child > 2: ID of parent > 3: Name of child > Problem is that the child may be a parent too thus he should be at the same time in the parent's table. > plus whatever other information you decide to keep on children. > > Notice how extensible this is. You can have an unlimited number of children > for each parent. > > You could treat this like a forum, too, and use a single table. > > 1. Unique id for each person > 2. ID of parent > 3. Name > etc... > > Where, column 2 would be NULL (or empty) if the person is at the "top" of > your chain (i.e. their parents aren't in the database). Everyone under them > (their children, grandchildren, etc) would be in the same table and have > column 2 as the ID of their parent (coming from the same table). > > Confusing? I hope not, but ask away if it is... :) I like this solution. It means, when I query the record of an individual that I have to look at all the records of the table to see who has the individual as parent to be able to reconstruct the family. In fact I made a table with parent_ID and ten fields for children_ID (bad solution I know). The parent_ID field (in fact mother_ID and father_ID) already exists and I just have to retype my scripts to reconstitute the family of a particular individual with backward looking at him from individuals who have him as parent. Thank You. Tell me if I misunderstood or if I did not express me clearly. > > ---John Holmes... -- Alain Barthélemy cassandre@bartydeux.be http://bartydeux.be