From: | <zen31329(at)zen(dot)co(dot)uk> |
---|---|
To: | <pgsql-php(at)postgresql(dot)org> |
Cc: | Matthias Weinhold <Matthias(dot)Weinhold(at)gmx(dot)net> |
Subject: | Re: array fields in the database and their handling with php4 |
Date: | 2003-10-26 18:56:11 |
Message-ID: | E1ADq3f-0000Kq-SN@heisenberg.zen.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Witney | 2003-10-27 08:59:18 | Re: array fields in the database and their handling with php4 |
Previous Message | Michael Glaesemann | 2003-10-26 16:30:03 | Re: Saving result to file for download |