design of datatype or object type

julla27 · #1 (**permalink**) 04-15-04, 08:50

Hi All,

i try to display the following structure:

table X:
=======
ID,
....,
CATEGORY

, whereby:

CATEGORY = NULL | WORD1 | WORD2| [ACEF]

Now is my question, what is the best solution:
1) to create a table for category, a table for [ACEF] with CHECK CONSTRAINT

or

2) to create a type for ACEF

or

3) to create just a CHECK CONSTRAINT for CATEGORY

or

4) ?

Thanks in advance!

julla27 · #2 (**permalink**) 04-15-04, 10:55

Solved:

PHP Code:


			


create table x   (id, ..., category); 

create table cat (category); 



ALTER TABLE cat ADD ( 

CONSTRAINT PK_cat PRIMARY KEY (category)); 





ALTER TABLE X ADD ( 

CONSTRAINT FK_X_cat FOREIGN KEY (category) 

REFERENCES cat(category)); 



ALTER TABLE cat ADD ( 

CONSTRAINT CH_cat 

CHECK (INSTR(TRANSLATE(category,'ACEFX','XXXXU'),'X') > 0 

OR category in ('WORD1', 'WORD2') 

));

Internet Services

Web Development

Digital Marketing

Consumer Tech