| From: | "Stuart Statman" <stu(at)slammedia(dot)com> |
|---|---|
| To: | "Mathijs Brands" <mathijs(at)ilse(dot)nl>, "Frank Joerdens" <frank(at)joerdens(dot)de> |
| Cc: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | RE: How to represent a tree-structure in a relational database |
| Date: | 2000-12-13 20:09:06 |
| Message-ID: | NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
> The way I'd do it if I had to do it again:
> Give each record a unique id, generated by the application.
> Denote levels with extra letters.
>
> So:
>
> AA - Automotive transport
> AAAA - Cars
> AAAB - Motorcycles
>
> The structures has the added bonus of making it very easy to
> determine all the
> sub-categories of a category, no matter how deep the tree is
> below the category
> you're looking at. With the first approach it is not possible
> to do this in a
> single SQL query. You could do this with a function, I guess.
The problem with this method is if you need to insert a category, or move a
category. You'll need to re-id a bunch of categories, and bubble those
changes out to every table that refers to this table.
Stuart Statman
Director of Software Development
Slam Media, Inc.
| Attachment | Content-Type | Size |
|---|---|---|
| Stuart Statman.vcf | text/x-vcard | 482 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Eisentraut | 2000-12-13 20:25:28 | Re: Decimal vs.Numeric vs. Int & type for OID |
| Previous Message | Frank Mingan You | 2000-12-13 20:00:16 | Query by sresultset.getArray(index) |