Re: How to Represent Enum Types and Relationships in Oracle

>>>>> "Ryan" == Ryan Gaffuri <rgaffuri_at_cox.net> writes:

 Ryan> "David Hammond" <david_hammond_at_yahoo.com> wrote in message  Ryan> news:<b066ci$o5f$1_at_wotsit.aston.ac.uk>...
>> Hi, I'm developing a object database in Oracle 8 but am having a
>> few problems represents enumeration types and relationships.
>>
>> I have used the ODBMG schema for a logical model and have the
>> following:-
>>
>> attribute enum credit-rating{poor,normal,good) credit-rating;
>>
>> and relationships of the type
>>
>> relationship set<order> placed by inverse order::places;
>> relationship customer places inverse customer::placed by;
>>
>> how do I implement these in Oracle 8, there seems to be no obvious
>> on-line help, which is strange?
>>
>> Thanks. David.

 Ryan> I dont think there is any innate functionality for this, so you  Ryan> have to use something else to simulate it. Examples:

 Ryan> 1. Look up table, with 2 columns. One for the Key Number, other
 Ryan>    value
 Ryan> 2. VARRAY stored in database. Use the index for your ENUM
 Ryan>    number

 Ryan> Might be able to do something with an object type. Dont know if  Ryan> this has been added to 9i?

You could define before insert/update triggers on the table which checks to make sure the values you are entering are one of your enumerated values and throw an error if its not. At least this would prevent "bad" values from being entered into the field. Alternatively, the above suggestion with an enumerated values table and a foreign key constraint on your fields.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!