Database Design Model RRS feed

  • Question


    I wanted to get your advice on something about Database design for our enterprise system that we are building.


    Background info:


    We are building a enterprise type system that will support the following type of applications:

    1)       Internal Retail / Auction System that will allow the users to process sales at there store or offsite and allow users to purchase inventory from customers in the same manner.

    2)       Web Site that will allow customers to purchase inventory.

    3)       Customer Support systems that will allow users to trouble shoot and query processed sales and purchase orders

    4)       Back End system that will process these transactions to and from our ERP system.


    My Question, Would it be better to separate out the data stores that are shared or common to each of these different types of systems.  Here is what I am thinking.

    1)       Have a separate Credit Card Database that will house the credit card data and transactions associated with sales for Retail, Website, customer support systems.

    2)       Have a separate Staging Database for the processing of transactions to and from the ERP system.

    3)       Have a separate Database for each type of system (Retail, Website, Customer Support), which houses data that is only dependent on that system.

    4)       Have a separate Database for application logging.


    There is a guy here who is arguing to have everything in one single database, but my fear is that having all the systems so tightly coupled to a single database will impact performance and scalability.


    Let me know your thoughts and pros and cons.

    Tuesday, January 27, 2009 1:40 PM

All replies

  • Credit card can be held as an entity in your retail, website, consumer, etc., or can be exist as a separate instance of its own exposed by some type of data service.  It should be dependent upon your business requirements and sensitivity to manage customer credit card information (Also look at PCI-DSS standards for credit card security guidelines)


    Unless your ERP system requires you to manage the processes asynchronously you really dont need a database.  Even in that case, I would use something like BizTalk Server or nServiceBus in a situation like this which is more suited for this type of requirement.


    I would recommend strongly against keeping a database for application logging.  Filesystem is a less intrusive logging approach than an external dependency like database.  Either way you choose, take a look at log4net or enterprise library offers several efficient ways to instrument your application(database being one of the sink).


    If there is less cross-functional elements among retail, website and customer support I would recommend keeping them in separate database instance for two reasons one more level of security and keeping the data design close to the entity model required by each application. 


    Finally, your "guy" might be correct to keep everything in one single database, if the volume, size, transactions per minute, support team structure, etc., does not warrant to manage all these separately.


    Hope this helps.



    Wednesday, January 28, 2009 4:25 AM