Data problems on SC

[JoeyA]

Our organisation is considering implementing SC5 using Oracle 8.1.7 as our DB. I have a concern about the lack of data integrity, even at app level. It seems as though SC is implemented more as a file system instead of a relational type database and over time could result in some serious data inconsistencies and data anomalies normally associated to pre-hierarchical/network database designs.

Questions
- Has anyone experienced data integrity issues relating to SC? For example, contacts belonging to non-existent locations.
- How did you overcome these problems?
- Does SC have any integrity checks and how can they be implemented?

 

[dx111ge]

no way from sc side , but you can use format control and validations to build some rules for this

 

[JoeyA]

Thanks dx111ge,

This was my concern because this is a huge task. Please note that I'm not that familiar with the tool and therefore may be asking an obvious question : Is there a way of determining the references (FK) to another table using P4 or it's data dictionary (I assume that there is one). The only mechanism I have is the oracle query (assume I searching for all references to company) :
select table_name, column_name
from cols
where lower(column_name) like '%company%'.

This however will only work if all company references have the word 'company' in them.

 

[dx111ge]

normally it works , but sometimes , the names are different ( relations between company and another field in another table with the name 'name'). what you need is a erd , but the ERD from peregrine isn't really helpfull, some relations are missing.

 

[doodvat]

We are currently consulting at a client who uses SC 5, and at first we also wanted to change the P4 DB over to a RDBMS. However, after doing some investigation, we decided to rather create a shadow RDBMS database. P4 is optimsised for performance, and we use the Shadow database for all our integrity and reporting requirements.