Re: Normalization and Design Question

Originally posted by Absinthe
> ...
> ContactAddresses(
> ID, <-- uniq id
> ContactID, <-- fkey to Contacts.ID
> Address,
> City,
> State,
> Zip,
> DefaultYN
> )
>
> Other than interface-wise I can see no way of enforcing this to
> have only
> one default address/phone. Though perhaps a trigger may handle it.
>
>

You could use a unique function-based index to enforce the rule:

create unique index ContactAddresses_index on ContactAddresses ( decode( DefaultYN, 'Y', ContactID) );

This would permit 0 or 1 defaults per Contact, and to change the default you would have to update the old record's DefaultYN to 'N' BEFORE changing the new record's DefaultYN to 'Y'.

--
Posted via http://dbforums.com