Re: FK relations...

Jacques,

We fought with this issue at one time, and decided NOT to implement sequence numbers. The main problem we could foresee with sequence numbers is that they could get out of sync when moving data around. We include some sample data with our system, and that complicates creating the sequences.

Our solution was to call an external 'C' routine from our embedded SQL (Pro*COBOL) which gets the operating system time in resolution of milliseconds and store that in a CHAR field. This system has been working for us with no problems for some time, but I think we may be suffering somewhat from performance because we are sorting, searching, etc. on a large 20 character field.

Will changing this field to a NUMBER from a CHAR help performance any in the manipulation of the index, sorting, etc.? We're looking for alternatives. By moving this from being an operating system to being a database function (getting a unique key) we give ourselves more flexibility.

Thanks,
Brian

Jacques Raymond Kilchoer <jrkilch_at_costcare.com> wrote in article <3317342D.6211_at_costcare.com>...

<snip>  

> Couldn't the tables be set up like this?
> Person:
> Person-id meaningless number, assigned via sequence
> SSN
>
> CAR:
> Person-id
> VIN number
>
> Car Parts:
> Person-id
> Vin number
> part number
>
> Maybe that's not feasible in your case.
> In general I try to use sequence values for any attribute used to
> uniquely identify a real-world object, especially is this attribute will
> be present in many tables. (e.g. in the three tables above perhaps it
> would be better to have a sequence instead of using VIN number in two
> tables)
> Of course there would probably be an index on SSN in the person table.
 

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/