SQL Server Performance Forum – Threads Archive
Design Advice
Hi, I am currently in the process of redeveloping a website includin g the back end database – lets say Im selling sporting goods and I have 5 very differently structured databases which are given to me by suppliers some are true relational product information database others are supplied as a single table – tennis goods,soccer goods,basketball goods, baseball goods and football goods. my question Is – is it bad design to keep all the web facing product data in their seperate databases (the tables may still reside within the same SQL server database) searching and browsing is written to access those databases. the *essential* information is added into my order table – i.e. what the customer ordered. This way I can change what I like on the product side of things but my administration system only looks at what is in the orders table. The only problems this presents that I can work out are for example what If i wanted to email all my customer who bought size 10 tennis shoes and there is no longer a record for tennis shoes in my tennis database – i would have to use my order level data for this query which means needing to hold much more information in my orders table than just a single key back to a main product table (this is it is currently setup – but to add a item into the users basket I first add a record into the main product table then call my add to basket code.). I do have information such as product barcode numbers which could possibly be used as a key between my orders table and my product tables. I can see pros and cons of each (although i am a mere sql beginner!) but I am swayed towards this satellite design – which is better from both good design and performance reasons bearing in mind I already have a few million rows of existing order data that I would need to populate my new database with. any advice appreciated!cDc
You are faced with a very big project, and offering specific advice is hard because we only have part of the facts (this is the nature of questions in a forum). Based on what you have described, I would take the approach I would design my own database structure that is properly normalized, then import the data from the other vendor’s databases. This is the only way to ensure that your database will be normalized, retaining its data integrity, and reaching optimum performance. If you use the databases as is (or even their table structures) you risk have a poorly performing database, plus the vendors provide you with updated data as products changes, it is possible that their database or table structure will changes, causing you all kinds of pain. Importing the vendors data may be a pain, but is a necessary evil. Once you get your database designed properly, the you can use DTS to import the data into your system as appropriate.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>