Wednesday, March 7, 2012

Database design question for experts

Hi,

I have 2 design related questions.

Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(add\update\search), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items).

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

Please give us your expert input\suggestions\tips that will guide our team to the right direction.

Thanks

JenniferParadise [ip]

The size of the database you're describing is nothing out of theordinary for an SQL Server instalation. More important than the numberof records is the number and type of queries you expect to be runningper second. If a single beefy (multi CPU Xeon with as much memory asthe system will take, fast IO subsystem) SQL Server can not handle theload then you could add more servers and build a cluster. I'm no expertat these matter and frankly if you need to come to this forum to learnabout these things the project may be a bit outside of your team'scapabilities. Assuming this is a professional, commercial applicationyou're probably better of hiring an SQL Server expert as a consultantfor a few weeks. But his or her effectiveness will be limited by howwell you know what you're trying to achieve.
In the meantime I recommend you do some experiments. Build a databaseserver and populate it with tables and arbitrary data. Then write someof the more complex and frequently called queries you expect to run.See how many of those you can run in a minute. That will give you someidea what performance limitations you're looking at.
One ASP.NET app that I wrote mines a database to generate a test resultreport. Depending on parameters it can run about 2000 queries, half ofthose are joins over 5 tables or more. The report completes in a minuteor two. This includes generating all the ASP.NET webcontrols etc. Themachine is a dual Xeon 2.8G with 2 GB RAM with mirrored SCSI drives.IIS and SQL reside on the same box.
I would not recommend splitting the application into multiple parts Idon't think there's a performance impact either way but you benefitfrom easier management in a single app. I'd be interested to hear otheropinions on this though.

Good luck!
|||

Nocturnal,

Thanks for your detailed post. Our 6 modules will have around 1 MILLION records in the database! even though we will be deleting old records regularly. Do you still call this an ordinary database for an ecommerce application? Here is more on the database internal: we create views by running complex join on the database involving ~10 tables. Each module has one big view against which we run the search queries for each module.

Having this said, do you still think ONE database with ONE web application suits our needs best?

What I am seeing from your comments is that making one WEB application will be easier to manage and performance impact won't hurt comparing to individual app for each module.

Thanks for your expertise valuable suggestions.

|||Hi,
first of all, I'm hardly an expert so take my suggestions with a grain of salt ok? :-)
I didn't mean to say that 1M records is normal for a e-commerceapplication but it's nothing out of the ordinary for SQL Server. SQLServer doesn't care what application stores its data there, after all.1 M e-ecommerce records is the same as 1 M cellphone records, inconcept.
1M records sounds like a big deal but it depends on the average recordsize as well. Imagine a 200 byte record. Loading the entire table inmemory will take only 200 MB plus overhead. Let's be conservative andcall the overhead 2x. That's 400 MB. Hardly worth mentioning on aserver that has 4 GB of RAM or more. Add some smart indexing and thistable can be searched very quickly.
There are volumes and volumes written about database optimization. Iknow very little about the subject and it's beyond what a forum canreally teach anyway. You should be able to learn the essence from acombination of studying the right books, experimentation and plain ol'experience.
It is good that you have some grasp of the scalability requirements ofyour application. Many designers don't. Now take it one step furtherand start experimenting with your database structures. Create somerepresentative tables and fill them with 1M random records. Then runthe queries against it that you expect your web app to run. Measureperformance. Measure server impact (watch for high CPU utilization andswapping). Modify the experiment, draw conclusions, refine your design.There's really no substitute for experimentation. Have a go at it andlet us know!
|||

jennifer7290 wrote:

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc.

You should probably start by having proper names for your tables. A table represents a SET of entities. "Modules", not "module". Also, the name "forsell" and "shopping" makes very little sense.What is a "shopping"? Use names that describe what the table contains. See ISO-11179.

jennifer7290 wrote:

The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

The single largest problem you'll face with many applications is the inability to share session state -- session state goes away at the application boundry, so you'll have to develop your own site-wide state management - which may not be so bad, anyway, as a lot of applications I see go with shared Context model.

As far as your database, it doesn't have to reflect your application. A database can have quite literally a crap load of tables (2B is the max I believe) and you'll be more than fine with a few hundred. Separate databases mean separate Roles, Users, etc -- and can be quite a pain to set up. I'd recommend sticking with one -- unlike the application, you have a common "security" module shared by each database.

|||

Hello, good question... a single database or multiple databases?

Let's consider

1) A table with 10 Milion of rows is not a problem for SQL server if we use a proper index

2) 10 Tables of 1 M of rows are better then a table of 10M of rows

3) Performace ofter depends on applications that use a database (use pooling)

4) Make minimal queries and use recorset in memory (1 query of 1000 rows is batter than 10 queries of 100 rows)

I suggest 1 DB.

Hello!

Paradise [ip]

|||Thanks to Alex and Claudio for sharing your valuable input. That really matters. Have a nice day!

No comments:

Post a Comment