By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

CHECK versus a Table for an enumeration

P: n/a
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
....
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);

CREATE TABLE user (
....
user_type_id integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
user(user_type_id));

I am really confused so I'll wait for your advices.

Thanks,

Melanie
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Mon, 22 Dec 2003, Melanie Bergeron wrote:
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));
Will you ever in your wildest dreams need more or different values in
user_type? If not them go here. I user this for well known, limited
sets - Male/Female.
or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);

CREATE TABLE user (
...
user_type_id integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
user(user_type_id));

I am really confused so I'll wait for your advices.


This when there is a chance, any chance, you'll need to add to the list or
make changes to user_type_desc.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

P: n/a

On 22/12/2003 21:37 Melanie Bergeron wrote:
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);

CREATE TABLE user (
...
user_type_id integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
user(user_type_id));

I am really confused so I'll wait for your advices.

Thanks,

Melanie


just my personal opinion but here goes:

For the example you've provided I don't think theres much in it. The
second version would give you ability to change the text of the user type
if that were important and to add new user types without having to alter
constraints. So if I _had_ to choose, I'd take the second option.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a
Thank you very much to you all. I'll choose the second option to be more
flexible.

Melanie
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4

P: n/a
On Mon, Dec 22, 2003 at 04:37:51PM -0500, Melanie Bergeron wrote:
I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);


Check out the second article at
http://www.varlena.com/varlena/GeneralBits/42.php

It contains some discussion on this issue.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #5

P: n/a
Hello Melanie

If your set of items will by static and small, then you can use CHECK
constraint. I use it for five, items itemes.

You can write more simple this constraint

....
user_type TEXT NOT NULL CHECK (user_type IN ('Root','Admin','Standard')),

regards
Pavel Stehule


On Mon, 22 Dec 2003, Melanie Bergeron wrote:
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));

or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);

CREATE TABLE user (
...
user_type_id integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
user(user_type_id));

I am really confused so I'll wait for your advices.

Thanks,

Melanie
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #6

P: n/a
On Mon, 22 Dec 2003, Melanie Bergeron wrote:
Hi all!

I want to know what's better between these 2 solutions :

CREATE TABLE user (
...
user_type text CHECK(user_type='Root' OR user_type = 'Admin' OR
user_type = 'Standard'));
Will you ever in your wildest dreams need more or different values in
user_type? If not them go here. I user this for well known, limited
sets - Male/Female.
or the following :

CREATE TABLE user_type(
user_type_id integer PRIMARY KEY,
user_type_desc text);

CREATE TABLE user (
...
user_type_id integer,
CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
user(user_type_id));

I am really confused so I'll wait for your advices.


This when there is a chance, any chance, you'll need to add to the list or
make changes to user_type_desc.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #7

P: n/a
Thank you very much to you all. I'll choose the second option to be more
flexible.

Melanie
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.