Req help with data types/domains

FAC51 · #1 (**permalink**) 10-30-04, 05:06

Hi,

I have a data type called ModelElement_t, the type contains 2 attributes: name & visibility. name can be represented as a string, only visibility is to be represented as an adt visibilityKind

CREATE TYPE ModelElement_t AS (name varchar, visibility visibilityKind)

CREATE TYPE visibilityKind_t AS (????

visibilitykind can be of 5 possible settings:vk_public, vk_protected, vk_private, vk_package, & vk_notapplicable. the default setting is vk_public.

could anyone give me any suggestions how to represent this, in conformance with the relational model? Im wondering if an array may be appropriate (only im not sure how to do this) or use constraint settings. I'd be appreciative if someone could display the SQL code. Any suggestions welcome.

Thanks in advance,

FAC51

andrewst · #2 (**permalink**) 11-02-04, 07:56

I believe the SQL approach would be like:

Code:

create domain visibilityKind_t varchar2(16)
constraint visibilityKind_chk check value in ('vk_public', 'vk_protected', 'vk_private', 'vk_package', 'vk_notapplicable');

I am less clear about the OO "create type" approach. This tends to vary from vendor to vendor. The way it works in Oracle, there can be no constraint on the TYPE itself, only on columns of the TYPE - i.e.:

Code:

CREATE TYPE visibilityKind_t AS OBJECT (kind VARCHAR2(16));

CREATE TYPE ModelElement_t AS OBJECT (name varchar, visibility visibilityKind);

CREATE TABLE x (id INT, ModelElement ModelElement_t,
CONSTRAINT visibilityKind_chk CHECK ModelElement.kind IN ('vk_public', 'vk_protected', 'vk_private', 'vk_package', 'vk_notapplicable')
);

... which is poor, in my opinion.

FAC51 · #3 (**permalink**) 11-02-04, 09:24

hi andrew, thanks for your reply, it was quite a tricky one i put out, i have a much better understanding now, thanks!

Quote:

Originally Posted by andrewst

I believe the SQL approach would be like:

Code:

create domain visibilityKind_t varchar2(16)
constraint visibilityKind_chk check value in ('vk_public', 'vk_protected', 'vk_private', 'vk_package', 'vk_notapplicable');

I am less clear about the OO "create type" approach. This tends to vary from vendor to vendor. The way it works in Oracle, there can be no constraint on the TYPE itself, only on columns of the TYPE - i.e.:

Code:

CREATE TYPE visibilityKind_t AS OBJECT (kind VARCHAR2(16));

CREATE TYPE ModelElement_t AS OBJECT (name varchar, visibility visibilityKind);

CREATE TABLE x (id INT, ModelElement ModelElement_t,
CONSTRAINT visibilityKind_chk CHECK ModelElement.kind IN ('vk_public', 'vk_protected', 'vk_private', 'vk_package', 'vk_notapplicable')
);

... which is poor, in my opinion.

Internet Services

Web Development

Digital Marketing

Consumer Tech