| Subject | Re: Schemas |
|---|---|
| Author | daltoncalford |
| Post date | 2009-09-24T14:42:14Z |
We use Schema's, in a variety of databases. (MS,Oracle,DB2, Pervasive)
1.) Sage Accpac uses schemas to group together a variety of different companies under a single database, with a common set of users and preferences (including their rights). This allows for consolidated corporate queries between different databases while client software points to a single schema for their own access. This is just one type of application where you have the same data/table structures within the same database but not holding the same data - you could do the same thing with a filter column and include that column as criteria for every query but schemas are simpler, and can have different security while the filtering column example would not have any security unless the client app or dba writes some sort of extra, non-standard security mechinism.
2.) When working on large databases, you may need to perform a short term OLAP study. If you do this outside of a schema, it is easy to have problems with after the fact cleanup. For example, the developer builds the processes/tables etc and uses names that are logical to them. unfortunately they are close in name to other processes/tables that perform other tasks (the infamous "foo" table or "temp" table that soon becomes "foo392" and "temp_for_john_072009") as the specification is for short term reports, the result is the developer does not spend much time in engineering the task. Going in after the fact and trying to figure out what is active, what is static and what is garbage becomes a logistical nightmare. Especially when you have multiple offices and the politics that goes with it. The new features in 2.5 for remote database access helps, but, the best solution would be to have schemas that can be pointed to remote databases. You define the schema and it can be local to the current database or pointing to a schema on a remote database/server. This would be very helpful in large databases where historical data is spread across multiple locations or when you have the problem with data being duplicated in different databases due to the lack of cross database queries.
3.) Schemas extend the life of firebirds 32 Char limitation to object names. You could now segment/organize your database in a more hierachal fashion and know what data /domains/procedures etc belong together (As you can see, I suggest that all database objects support the schema extensions).
4.) Many schools use Schemas to separate out the work of one student from another. It allows for many developers to be granted access to one physical database while not having them worry about the actions of any other developer.
These are just the off the top of my head answers, there are many more reasons, but I have yet to have my coffee.
best regards
Dalton.
1.) Sage Accpac uses schemas to group together a variety of different companies under a single database, with a common set of users and preferences (including their rights). This allows for consolidated corporate queries between different databases while client software points to a single schema for their own access. This is just one type of application where you have the same data/table structures within the same database but not holding the same data - you could do the same thing with a filter column and include that column as criteria for every query but schemas are simpler, and can have different security while the filtering column example would not have any security unless the client app or dba writes some sort of extra, non-standard security mechinism.
2.) When working on large databases, you may need to perform a short term OLAP study. If you do this outside of a schema, it is easy to have problems with after the fact cleanup. For example, the developer builds the processes/tables etc and uses names that are logical to them. unfortunately they are close in name to other processes/tables that perform other tasks (the infamous "foo" table or "temp" table that soon becomes "foo392" and "temp_for_john_072009") as the specification is for short term reports, the result is the developer does not spend much time in engineering the task. Going in after the fact and trying to figure out what is active, what is static and what is garbage becomes a logistical nightmare. Especially when you have multiple offices and the politics that goes with it. The new features in 2.5 for remote database access helps, but, the best solution would be to have schemas that can be pointed to remote databases. You define the schema and it can be local to the current database or pointing to a schema on a remote database/server. This would be very helpful in large databases where historical data is spread across multiple locations or when you have the problem with data being duplicated in different databases due to the lack of cross database queries.
3.) Schemas extend the life of firebirds 32 Char limitation to object names. You could now segment/organize your database in a more hierachal fashion and know what data /domains/procedures etc belong together (As you can see, I suggest that all database objects support the schema extensions).
4.) Many schools use Schemas to separate out the work of one student from another. It allows for many developers to be granted access to one physical database while not having them worry about the actions of any other developer.
These are just the off the top of my head answers, there are many more reasons, but I have yet to have my coffee.
best regards
Dalton.
--- In Firebird-Architect@yahoogroups.com, "Claudio Valderrama C." <cvalde@...> wrote:
>
> Hello.
> Not sure this is the right time to post the subject, but since I was already
> writing a letter when someone mentioned schemas in fb-devel...
>
> Our parser treats "schema" as synonym for "database" for historical reasons.
> Not having read the standard regarding schemas but some examples in the net,
> I have a rough idea:
>
> - a db is divided into schemas
> - there's a default, implicit schema (that may or may not be mentioned
> explicitly)
> - the catalog (system tables) lives in its own schema (don't know how
> relevant it's, but I found it in other products)
> - a schema can have security (and new privileges are needed regarding the
> right to execute DDL, anyway)
> - a schema can change its owner
> - objects can be moved between schemas (what a mess)
> - relationships should work between objects in different schemas
> - dropping a schema gets rid of all objects in it (but cannot be done if
> another schema references some object there, of course)
> - a schema is a logical partition and has nothing to do with object
> placement techniques like "tablespaces"
>
> Now, who sees schemas as an urgent need and why?
>
> C.
> ---
> Claudio Valderrama C. - www.cvalde.net
> Consultant, SW developer.
>