Thanks to both Ed and Amer for the excellent suggestions.
This is definitely the time in my project for db changes :) I think you've
both nailed what fundamental I was missing. In the long run, a bit of
re-thinking now will simplify a lot down the road.
Ed, sorry to be so dense, but by PK field do you mean a unique ID field?
Amer, re my mail bouncing... My host had numerous issues yesterday.
Hopefully all fixed today. Oh, and thanks for the book suggestion :)
Best regards,
Verdon
On 8/12/03 6:05 AM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>> On Monday, August 11, 2003, at 11:06 AM, Verdon vaillancourt wrote:
>>
>> 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'
>
> From: Ed Leafe <[EMAIL PROTECTED]>
>
> 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.
>
>
> From: Amer Neely <[EMAIL PROTECTED]>
>
> It may be possible to cruft a query for what you want, but I'd suggest a
> slight re-design of your tables. Do some reading up on designing
> relational databases ("Database design for mere mortals" by Michael J.
> Hernandez is an excellent choice. ISBN: 0-201-69471-9).
>
> Any time you have more than one value in a field is an idication you
> should consider making that field a separate table. In your case I'd
> make a new table of 'pieces' and populate it with something that
> identifies which client AND project each piece belongs to. Maybe a
> combination of 2 fields (client id + project id). Then your select would
> include 2 'where' conditions checking client id and project id.
>
> btw, your email address bounced back - couldn't check the MX records.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
