Models per database best practice?

[pincq]

In the system I inherited, we have a PSQL model/database for each project, totalling 24 projects.

This provides separation and access control for sensitive projects, but restricts sharing objects between projects. I remember a conference video from a couple of years ago, where the presenter recommended a single database containing every project.

I am looking at trialling PCS to replace our legacy system, and the difference between team and enterprise edition is 7 models. So it may be a good time to review our approach to systems models.

Is there a generally accepted or recommended best practice for models-per-database?

[Modesto Vega]

We use a single repository/database with a view per system, a view for the enterprise data architecture, and a view for the projects with a package per project. 

[ea0522]

You could use the user / group security mechanism.
Optionally define a group for each sensitive project and lock it for access only by the members of the group.
To prevent too much groups you maybe can 'group' some projects under a single group ID.
When using a database, you could also try row-based security on the records in the database.

[BobM]

If you are using PCS and its webservices then the only way to currently secure the data is by splitting up the databases.

Unfortunately currently in 5.1.126 the search button is not restricted by row-based security and any model can be found.
I personally consider this a major bug, but blocking sql scratchpad or certain scripts isn't possible in the client neither.

In diagram navigation works as well (but in our opinion that is a good thing)

[Geert Bellekens]

In a single database you can set write restrictions, but not really view restrictions.

So if every user is allowed to read every project you can put them into one database.

Geert

[pincq]

Thanks all.

I need view restrictions on sensitive data, so it seems like I will have to consider the full blown Enterprise edition.

[Geert Bellekens]

If you only want to use it to connect to the databases (without things like WebEA or prolaborate) I think you can use the free edition.

Geert

[pincq]

Yes, I've got free PCS working (licencing, hosting). One of the reasons to replace the old keystore was to use WebEA to engage with stakeholders

[BobM]

Yes, I've got free PCS working (licencing, hosting). One of the reasons to replace the old keystore was to use WebEA to engage with stakeholders

WebEA is great if set up properly, but as I mentioned before the search function will allow you to search everything in the database

We personally removed breadcrumbs (as it might allow people to access stuff they shouldn't), and some other settings (I think I posted our setup somewhere in this or the PCS forum)

our entire company WC population (around 1k users has a base access to it with specialized groups for additional views)
Its easy to maintain SSO with access for externals as well (via a different route but not noticeable for outsiders each group having different views)

[Modesto Vega]

I have used in the past groups to control write permissions but as mentioned elsewhere in the thread it does hide anything from view - i.e., there is no way to control read permissions. Groups works with locking have worked well in the past, but your typical Sparx EA user is very curious and is always looking into areas of the repository they cannot.

We also use PCS and WebEA but have not yet settle into a pattern similar to the one described by Bob. The pattern he describes is possibly the most optimal pattern for your needs.