Skip site navigation (1) Skip section navigation (2)

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

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-php
Matthias Weinhold <Matthias(dot)Weinhold(at)gmx(dot)net> wrote :


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, 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!


> 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

pgsql-php by date

Next:From: Adam WitneyDate: 2003-10-27 08:59:18
Subject: Re: array fields in the database and their handling with php4
Previous:From: Michael GlaesemannDate: 2003-10-26 16:30:03
Subject: Re: Saving result to file for download

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group