This content has been marked as final. Show 11 replies
user9954330 wrote:No. Let me rephrase that for you. The reuse of sequence numbers is idiotic.
We use this generate identifiers for specific entities, where reuse of sequence numbers is mandatory.
It is not an Oracle issue. It is not a technical issue. It is a design issue.
Show me your data model. And I will show why it is idiotic.
I just simplified the example with sequence number. But the column is used for identifying a connection between two locations. It totally consists 30 characters out of which 3 characters form the serial number. When a connection is removed, the existing serial number should be reused for new connection and new identifier should be generated with recycled serial number.
user9954330 wrote:And that is why this is a *<font color="Red">FAIL</font>*.
But the column is used for identifying a connection between two locations. It totally consists 30 characters out of which 3 characters form the serial number.
A connection is typically defined from entity A to entity B. Standard data modelling says that the connection entity will then be defined as follows:
CONNECTTION = ( UNIQUE_ENTITY_ID_FROM, UNIQUE_ENTITY_ID_TO )
A data model that defines that connection relationship as a single column of 30 characters that contains a complex data structure (serial number embedded in the value) is idiotic.
And yes, the term idiotic is the correct one to use.
Unless you can substantiate the +30-column-with-embedded-serial-number+ design for defining what should be a relationship for a basic vector?
user9954330 wrote:A network connection provides connectivity between point A and point B - where these can be IP addresses (as in a typical <i>traceroute</i>), router interfaces, port switches and so on.
This connection is not a normal entity to entity connection. I am referring to network connectivity here.
Network connectivity is exactly what our Oracle clusters and data models deal with (for networks with over 20,000 devices). So this is not exactly an unfamiliar topic.
OK. So we generate ConnectivtyID to the connection (Pipe). So we generate the identification for this Pipe with location end points and a serial number. When we delete the Pipe, we would like to reuse the serial number. Now that requirement is clear to you, can you suggest a soliution if you have one?
I think the answers were based on the fact that you call it incorrectly sequence.
Your ConnectivityID is more as a resource that could be recycled. I see some similarities to the GSM telecom market where a resource(i.e. a phone number or a SIM card no) can be recycled when is not active anymore (under certain conditions however).
Personally I would generate a table of resources having the resource number (ConnectivityID) and resource status.
To simplify it, suppose that you have column status with 2 possible values, 0=available, 1=not available.
When you want to allocate the resource number(ConnectivityId) you will set the status to 1.
When the record is deleted then status will be updated back to 0.
Recycling a ConnectivityID will mean searching in the table for the lowest number having status=0.
Of course you don't expect this to be as fast as a sequence but I think this is a different requirement, IMO.