Re: [PHP-DB] several pointers to records in one field
The standard relational way is a relationship table: children - FK_parentID (parent) FK_childrenID (child) PRIMARY KEY ( FK_parentID, FK_childrenID ) HTH Ignatius - Original Message - From: Alain Barthélemy [EMAIL PROTECTED] To: php-db [EMAIL PROTECTED] Sent: Monday, March 17, 2003 6:36 PM Subject: [PHP-DB] several pointers to records in one field Hello, 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? -- Alain Barthélemy [EMAIL PROTECTED] http://bartydeux.be -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
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? NO!!! Don't do that... that's horrible database design. :) 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 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... :) ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
Le lundi 17 mars 2003, 16:12:32 ou environ CPT John W. Holmes [EMAIL PROTECTED] 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? NO!!! 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 [EMAIL PROTECTED] http://bartydeux.be -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
Le lundi 17 mars 2003, 22:04:08 ou environ Ignatius Reilly [EMAIL PROTECTED] a écrit: The standard relational way is a relationship table: children - FK_parentID (parent) FK_childrenID (child) PRIMARY KEY ( FK_parentID, FK_childrenID ) HTH 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? Thank you. I thought of a relational database of course with what I already worked a few years ago with Perl-TK/MySQL. I may have badly understood but I'll still need a field per child to point toward the record in the children table and the parent is a child too. -- Alain Barthélemy [EMAIL PROTECTED] http://bartydeux.be -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
Le lundi 17 mars 2003, 15:19:07 ou environ May, Patrick [EMAIL PROTECTED] a écrit: The table structure I can imagine: person personId name etc relationship relationshipId description personToPersonRelationship -- fromPersonId toPersonId relationshipId The flexibility in here is to let you model pretty much any relationship between two people. I don't know your data, but this may be helpful (To be able to express step mothers, step fathers, nannies, etc) ~ Patrick Thank you Patrick. I keep the proposition but I'll try the proposition of Johh Holmes. -- Alain Barthélemy [EMAIL PROTECTED] http://bartydeux.be -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
Thank You. Tell me if I misunderstood or if I did not express me clearly. Sounds like you got it! :) You may also want to read up on nested sets for your database structure. They work the parent_id and child_id differently and it may make queries easier for your situation. For instance, with nested sets, you can easily find out everyone that decended from a single person with one query. Doing that with the parent_child solution can take many queries. However, inserting is more demanding with nested sets, but if you're not going to do a whole lot of inserting after the database is populated, then it may be for you. Here's one explanation... search for others: http://threebit.net/tutorials/nestedset/tutorial1.html#t ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] several pointers to records in one field
Here's one explanation... search for others: http://threebit.net/tutorials/nestedset/tutorial1.html#t Here's the example I was looking for... it took me a while to find it: http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html It's good reading for anyone working with databases to give you a look at alternate methods of designing your tables... ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
