hi I have a couple of data access questions - if you feel these questions are inappropriate for this forum please suggest another more appropriate one - thanks.
i'm developing a website (using spring) that sells services on behalf of independent businesses (tickets to local events,gift coupons for services (beauty treatments etc))
my first question is this : would it be better to have one large database with all the businesses information in or would it be better to have a seperate database for each business. One large database would give better performance but you would have to restrict access to tables(to ensure one business does not update anothers details). Many smaller databases would be easier to set up but the performance would be worse as you would have to use distributed transactions.
second question : i would like to keep some information locally at each business so they can (at least) verify the purchase (speed of verification is important), and perhaps enhance the service using the customer's profile (language preference etc). i am unsure of the strategy i should use to populate/maintain these 'local' databases (distributed across a WAN). should this be performed in the data layer by the database (using features such as replication) or should this be performed in the application layer (resulting in a distributed transaction)?my gut would say if there is any business logic involved it should be in the application layer, but i dont want to create distributed transactions 'just because i can'.
if you have experience in this area your two cents would be like gold to me ;-)