Thread: Datatype SET or enumeration type ?

Hello smart people,

I've been mining the web for an explication but alas I haven't found one.
So here is my the problem I want to solve in a nice way.

In a table I need a datatype that contains one of x possibilities, where x
is fixed from the beginning.
I could use an 'int2' type for instance, but that makes the desing unclear,
otherwise I could make
a separate table wich contains the real-world translations but that seems
way over kill.

Here is an example of  what I actualy mean:

******************
The int2 solution :

    create table order (
        "order_id" serial primary key,
        "order_status" int2 not null /* where 1=on hold, 2= on order,3=in
backorder,4=instock,5=sold */
        ETC ...
    ); /* end create table order

******************
The extra table solution :

    create table order_status (
        "order_status_id"    int2 unique not null
        "order_status_descr"    varchar(20)
    ); /* end create table order_status */

    create table order (
        "order_id" serial primary key,
        "order_status_id" int2 not null references order_status on update no
action,
        ETC ...
    ); /* end create table order */


Life would be easier and the database design nicer if there where a solution
like :

    create set "order_status_set" with {"on hold","on order","in
backorder","in stock","sold"}

where the internal representation could be an integer ...

so that I could use it as follows :

    create table order (
        "order_id" serial not null primary key,
        "order_status" order_status_set    not null default("on hold"),
        ETC ...
    ); /* end create table order */

The way I see it, the datatype SET ( a set of tuples) would be the way to do
it, however I cannot find
any detailed information about it. On Postgresql 7.2.3 I cann't even create
a column with type SET.

Any suggestions, solutions or explanations ?

Thanks and all the best for the new year ;-)

Peter Depuydt

Would "create type" be the solution you are seeking? Ie. Create a
user-defined type with the internal and external representations you desire
and create a table with a column of that type.

Cheers,
Steve

On Thursday 02 January 2003 7:55 am, Peter Depuydt wrote:
> Hello smart people,
>
> I've been mining the web for an explication but alas I haven't found one.
> So here is my the problem I want to solve in a nice way.
>
> In a table I need a datatype that contains one of x possibilities, where x
> is fixed from the beginning.
> I could use an 'int2' type for instance, but that makes the desing unclear,
> otherwise I could make
> a separate table wich contains the real-world translations but that seems
> way over kill.
>
> Here is an example of  what I actualy mean:
>
> ******************
> The int2 solution :
>
>     create table order (
>         "order_id" serial primary key,
>         "order_status" int2 not null /* where 1=on hold, 2= on order,3=in
> backorder,4=instock,5=sold */
>         ETC ...
>     ); /* end create table order
>
> ******************
> The extra table solution :
>
>     create table order_status (
>         "order_status_id"    int2 unique not null
>         "order_status_descr"    varchar(20)
>     ); /* end create table order_status */
>
>     create table order (
>         "order_id" serial primary key,
>         "order_status_id" int2 not null references order_status on update
> no action,
>         ETC ...
>     ); /* end create table order */
>
>
> Life would be easier and the database design nicer if there where a
> solution like :
>
>     create set "order_status_set" with {"on hold","on order","in
> backorder","in stock","sold"}
>
> where the internal representation could be an integer ...
>
> so that I could use it as follows :
>
>     create table order (
>         "order_id" serial not null primary key,
>         "order_status" order_status_set    not null default("on hold"),
>         ETC ...
>     ); /* end create table order */
>
> The way I see it, the datatype SET ( a set of tuples) would be the way to
> do it, however I cannot find
> any detailed information about it. On Postgresql 7.2.3 I cann't even create
> a column with type SET.
>
> Any suggestions, solutions or explanations ?
>
> Thanks and all the best for the new year ;-)
>
> Peter Depuydt
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html