2) I have a table of 'projects', which are comprised of a number of
'pieces'. There is a column in this projects table called 'pieces', which
contains a delimitated string of all the 'pieces' related to that 'project',
in the format of '1||2||3||5||12||16||17||24||25'
While this is a clever approach, a more robust approach is to create a third table, usually referred to as an allocation table or a many-to-many table, which contains the links between pieces and projects. Assuming that both Pieces and Projects have PK fields, the allocation table would consist of 3 columns: its own PK, the PK of the Project record, and the PK of the Pieces record. There would be as many records for a given Project PK as there are Pieces in the Project.
You can then join the Project to its Pieces as follows:
select Project.*, Pieces.* from Project left join ProjPieces on Project.projPK = ProjPieces.projPK left join Pieces on ProjPieces.piecePK = Pieces.piecePK where Project.projPK = [some value]
Note that using this design, it is just as easy to get all the pieces for a given project as it is to get all the projects a given piece belongs to.
___/
/
__/
/
____/
Ed Leafe
http://leafe.com/
http://opentech.leafe.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
