Re: array fields in the database and their handling with php4

Matthias Weinhold <Matthias(dot)Weinhold(at)gmx(dot)net> wrote :

Hi!

In deference to database normalisation (and a personal liking for explicit data structuring), I would represent this data in SQL via three tables along these lines (apologies - my german is next to non existant, so in English)..

Workers (WorkerID - PK, int, Name - varchar)

Sections (SectionID - PK, int, Title - varchar)

Workers_Sections (WorkerSectionID - PK, int, WorkerID - FK to Workers.WorkerID, SectionID - FK to Sections.SectionID)

So, each person and each section is represented by one row in their own 'type' table, and a third table links the two together, allowing for a many-many relationship. For each section which a person works in, they would have a row in Workers_Sections.

To get a person and all of their sections:

SELECT w.name, s.title
FROM workers w
INNER JOIN workers_sections ws on ws.workerid = w.workerid
INNER JOIN sections s on s.sectionid = ws.sectionid

Or alternately, you could retrieve the person you want, and then simply get all of their sections using the 'glue' table workers_sections and the sections table. This might be more logical, depending on how you process the data in PHP.

Hope this helps - Cheers!

--Matt

> Hey,
>
> i have a table 'mitarbeiter' in my database, column 'Abteilung' is an
> array{'Abteilung1, Abteilung2'}, because some people work in more than
> one section. Now i will print the results from a query like
>
> SELECT name, abteilung[1], abteilung[2],FROM mitarbeiter WHERE name =
> D. Benner;
>
> to an html-table via php in this form:
>
> Name | Benner
> -------------------
> Abteilung 1 | abt_1
> -------------------
> Abteilung 2 | abt_2
>
> I don't no how to handle the array-field in the database? Any hints?
>
> Greetings from cold germany Matthias
>
> --
> Die Asiaten haben den Weltmarkt mit unlauteren Methoden erobert - sie
> arbeiten während der Arbeitszeit.
> -- Ephraim Kishon (eigentlich: Ferenc Hoffmann)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster