Current Oracle Spatial Blog Articles
Over on the Oracle Technology Spatial Forum this question was asked:
(The article is here )
Here is my answer repackaged for this blog.
The questioner’s question can be turned around to point to a more generic question (for remember: “spatial is not special”!).
Do database tables with date columns mind if you store the same date more than once? Same for numbers, strings etc?
Clearly, the answer is no!
Now, if a duplicate date was not allowed for the same observation of a plant in a table called FLORA, we would stop this by creating a unique constraint on the table:
So the issue about what constitutes valid duplication or not is the way you implement constraints on the table.
Constraints and Models
Constraints reflect the “business rules” that constrain the data you are storing so that it is “fit for [your] purpose”
So, in the end, it is all about the data model.
One should start all data storage by creating a data model that exposes the rules you want for the data.
So, for our OBS_DATE column in our FLORA table we may wish that the values entered are valid. But we define valid to mean from 1st January 2005 onwards.
SQL Developer 3.x has a free data modelling tool built in: it is an excellent tool for designing basic data models.
It is difficult to implement uniqueness constraints that involve sdo_geometry data. Perhaps one day our SQL/OGC standards bodies and implementing database vendors will catch up on this problem.
Even so, we can do something. For example, if the flora table had an sdo_geometry column, called location, which was a point, this will work:
However, this would not work for linestring/polygon data. For example, let’s assume the FLORA table allowed observations of a plant to include a patch ie not a single plant (eg a tree) but an area of blackberries. We can’t do the following because one cannot reference a function in the constraint:
OK, so how can we do this?
AFAIK, we can only do this via a trigger:
So, in short, you can store anything in any Oracle table. In a table with 1 million rows you could have:
1. Only 2 unique values of an column called SPECIES.
But whether this is right or not (garbage) depends on the model and the constraints that represent “correctness”.
Folks, the best advice I can ever give you is: Start with the model.