Oracle Multiple schemas Vs single schemas
-
We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database. The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future. My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security. (But what if a table that starts belonging to one application later needs to be shared among applications?) Another option would be to seperate tablespaces, but use the same schema. I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill. Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case. Many thanks in advance.
V.
-
We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database. The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future. My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security. (But what if a table that starts belonging to one application later needs to be shared among applications?) Another option would be to seperate tablespaces, but use the same schema. I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill. Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case. Many thanks in advance.
V.
V. wrote:
His main reason being security.
Good reason.
V. wrote:
But what if a table that starts belonging to one application later needs to be shared among applications?)
Then you have to set the access rights accordingly and add the schema to the tablename when accessing it. Like this:
Select * from schemaname.tablenameV. wrote:
Another option would be to seperate tablespaces, but use the same schema.
Thats something completely different. That's a logical storage space[^].
-
We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database. The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future. My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security. (But what if a table that starts belonging to one application later needs to be shared among applications?) Another option would be to seperate tablespaces, but use the same schema. I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill. Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case. Many thanks in advance.
V.
I'm pretty sure tablespaces have nothing to do with your segregation of data, it is only for logical storage. We have just run across a real need for additional schemas, we want to move the production data (ETLd from other systems) to the dev server, if the data was in different schemas this would be simple, now we have to reengineer the database schemas to meet that requirement. Security is another issue so I would go for the additional schemas from the start. As for moving from 1 schema to anoter I believe there are tools to help (Toad) and it is just the price you pay for getting it wrong (or changing requirements).
Never underestimate the power of human stupidity RAH
-
We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database. The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future. My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security. (But what if a table that starts belonging to one application later needs to be shared among applications?) Another option would be to seperate tablespaces, but use the same schema. I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill. Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case. Many thanks in advance.
V.
The only place I worked with Oracle we had one big happy database and it was fine. Keep it simple.
-
We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database. The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future. My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security. (But what if a table that starts belonging to one application later needs to be shared among applications?) Another option would be to seperate tablespaces, but use the same schema. I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill. Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case. Many thanks in advance.
V.
Hi, Somekind of list of thoughts on this issue: - security is a good point. Although security can be enforced on many different levels you can use owner permissions for example in packages. This helps to handle common security problems. - logical design. If you separate the objects to different schemas based on the data they hold, it's easier to understand the database design (and usage) especially in bigger databases. - backups and other DDL operations. Many of the operations can be done schema based. For example you can export a certain schema so this helps in administrative tasks - usage in applications. This is two folded, you can either define the schema in applications (like
SELECT ... FROM _schema.table_) or you can use public synonyms to hide the structure. Public synonyms are bad in situations where you actually need for example a table with the same name twice in the database but otherwise they should simplify the use in the apps. - isolation. Using different schemas you can isolate different portions of the database more easily (a bit same as the security) but again this can be done in multiple ways.V. wrote:
Another option would be to seperate tablespaces, but use the same schema
This is a totally different issue. Tablespace is used to define the actual storage place for an object regardless of the schema. A simple situation is that you have a table and an index on it. In optimal case they are stored on different disks so they would be stored on different tablespaces even though they both would be defined in the same schema. So tablespace is just a way to define physical storage place without defining the actual file (since tablespace can contain multiple files).
The need to optimize rises from a bad design.My articles[^]
-
Hi, Somekind of list of thoughts on this issue: - security is a good point. Although security can be enforced on many different levels you can use owner permissions for example in packages. This helps to handle common security problems. - logical design. If you separate the objects to different schemas based on the data they hold, it's easier to understand the database design (and usage) especially in bigger databases. - backups and other DDL operations. Many of the operations can be done schema based. For example you can export a certain schema so this helps in administrative tasks - usage in applications. This is two folded, you can either define the schema in applications (like
SELECT ... FROM _schema.table_) or you can use public synonyms to hide the structure. Public synonyms are bad in situations where you actually need for example a table with the same name twice in the database but otherwise they should simplify the use in the apps. - isolation. Using different schemas you can isolate different portions of the database more easily (a bit same as the security) but again this can be done in multiple ways.V. wrote:
Another option would be to seperate tablespaces, but use the same schema
This is a totally different issue. Tablespace is used to define the actual storage place for an object regardless of the schema. A simple situation is that you have a table and an index on it. In optimal case they are stored on different disks so they would be stored on different tablespaces even though they both would be defined in the same schema. So tablespace is just a way to define physical storage place without defining the actual file (since tablespace can contain multiple files).
The need to optimize rises from a bad design.My articles[^]
This is in fact what we did finally. Created schema per application and a common one holding configuration data and parameters etc. For that schema we made public synonyms. After everything was moved I had to change very little to my prototypes to make them work :-) thanks for the advice.
V.
-
This is in fact what we did finally. Created schema per application and a common one holding configuration data and parameters etc. For that schema we made public synonyms. After everything was moved I had to change very little to my prototypes to make them work :-) thanks for the advice.
V.