Mohammad wrk wrote:
Hi Eric,
In the case of a "yes" answer to the second question below, can't we
still use something like VPD (Virtual Private Database) in MySQL?
Thanks,
Mohammad
Hi,
I don't know much about Oracle, but I looked this up. MySQL can't do it,
that I know of. Here is the final example of such a setup that I found
at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
CONNECT user1/[EMAIL PROTECTED];
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User2',2);
COMMIT;
CONNECT user2/[EMAIL PROTECTED]
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
CONNECT user1/[EMAIL PROTECTED];
SELECT * FROM schemaowner.user_data;
CONNECT user2/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
Notice that:
* When connected to USER1, only the first insert will work.
* When connected to USER2, only the second insert will work.
* The failing inserts produce the error:
ORA-28115: policy with check option violation
You can setup column level privileges on MySQL, but I wonder if it would
be buggy considering I have never heard of anyone doing this before.
Plus from what I understand the above example is a lot more than column
privileges. user1 can only insert data if the insert statement's data
sets user_id to 1, for example. Pretty cool, but scary in a way. I find
this much logic in the DB to be scary(esp if not well documented), but
then I use MySQL :)
So as to which way you should go is most defiantly a matter of opinion I
think. But, going back to my opinion(which is not at all informed as to
all the details), if question 2 is a YES, then I would tend to go with
separate DBs.
Thanks for the Oracle lesson :)
Thanks,
Eric
----- Original Message ----
From: Eric Frazier <[EMAIL PROTECTED]>
To: Mohammad wrk <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Monday, November 19, 2007 7:42:13 AM
Subject: Re: Giant database vs unlimited databases
Mohammad wrk wrote:
> Hi,
>
> I'm working on a web 2.0 project that targeting small to medium size
companies for providing business services. Companies simply register to
the site and then start their business by loading their data, sharing
and discussing them with others.
>
> The design/architectural decision now we are facing from database
perspective is how we should store companies' specific data? One way is
to put all of them in a single database and partition them by company-id
and the other one is to create, on the fly, a new database per company
. The justification for the latter is that MySQL is not powerful enough
(compare to Oracle or DB2) to handle large amount of data and concurrent
users.
>
> I'm new to MySQL and don't know that much about it and this is why
I'd like to discuss this concern here.
>
Funny, I thought you asked the question, should I separate my customers
into their own databases, or use one big DB? Not MySQL sucks, Oracle is
better. :)
Issues I would ask about on this:
1. Is there a chance that given their separation, these DBs will ever
diverge in design because of differences between customers?
2. Could they ever need to be separated for legal reasons? (like one bad
query causing customer data be compromised)
3. Is there any other reason you may do something vastly different from
one customer to another?
If you answer yes to any of these, then you might be best off separating
dbs. But, if you never want to, or expect for any of these things to
happen, you will just be creating headaches for yourself. Backup,
replication, and the need for cross DB queries, will all be a pain in
comparison to a single DB.
I am sure there is more to consider, but these are the points that come
to my mind right away.
Thanks,
Eric
------------------------------------------------------------------------
Instant message from any web browser! Try the new * Yahoo! Canada
Messenger for the Web BETA*
<http://ca.messenger.yahoo.com/webmessengerpromo.php>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]