"Dennis Lee Bieber" <wlfr...@ix.netcom.com> wrote in message
news:lrr67al6ppa852agu9rq2dstqtue17i...@4ax.com...
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <fr...@chagford.com>
> declaimed the following:
>
>>
>>Originally I supported two databases - PostgreSQL and Sql Server. They
>>both
>>have a concept called 'schemas', which handles my requirement elegantly -
>>each company's data is stored in its own schema.
>>
>
> We must have a different impression of what a "schema" consists of. As
> I learned it, the "schema" basically came down to the statements defining
> the form of the data, the rules (triggers) on it, and the relationships
> between data items. http://en.wikipedia.org/wiki/Database_schema
>
I also find it confusing. The same word is used by different RDBMS's to mean
different things.
When using sqlite3 in interactive mode, you can enter '.tables' to list all
tables in the database, and you can enter '.schema {table_name}' to view the
original DDL statement used to create the table.
>From the PostgreSQL docs -
"""
A database contains one or more named schemas, which in turn contain tables.
Schemas also contain other kinds of named objects, including data types,
functions, and operators. The same object name can be used in different
schemas without conflict; for example, both schema1 and myschema can contain
tables named mytable. Unlike databases, schemas are not rigidly separated: a
user can access objects in any of the schemas in the database he is
connected to, if he has privileges to do so.
"""
>
> For a client-server database system, with ability to restrict access
> based upon user accounts/host address, there are a number of ways to
> address the separation.
>
> Either each user account (company) gets its own database in the server
> -- possibly using the same pre-packaged DML to define the tables/etc --
> and
> one uses the access control system to prevent an account from even knowing
> there are other databases out there... Or... there is one set of tables
> (one database) containing data from all the companies -- wherein each
> table
> has a field identifying the company, and ALL accesses to the data is via
> views that incorporate a select clause restricting the data to the current
> logged in company.
>
I find that schemas gives me the advantages of both without the
disadvantages.
The disadvantage of using separate databases is that I want the flexibility
to allow one company to access data from another company, under controlled
conditions, specified by various parameters and permissions. It might be
possible to accesss a remote database with some systems, I don't know, but
schemas make it easy.
The disadvantage of using a single database, with a column specifying the
company, is that I want the flexibility to manage different companies, with
different data requirements, within the same system. A common database would
result in a mish-mash of different tables, some of which only relate to one
company or group of companies, and other tables relate to others. Using
schemas, I create one schema per company, which contains only the data
relating to that company.
Frank
--
https://mail.python.org/mailman/listinfo/python-list
