Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cDc
Starting Member
30 Posts |
 Posted - 2002-11-04 : 14:12:15
|
| Hello, I am currently looking for advice on the design for my database. my data started life as one quite small product catalogue but now I have four or five pretty big product catalogues ( lets say 150,000 products in each catalogue) from various sources which I would like to browse via an online shop. Currently I take the approach of having one extremely large catalogue and my ASP frontend add entries into this from the 'satelite' databases when the users add to their basket. the id field of this table is then my primary key into my order fulfilment system. This works ok but I am ending up with masses of duplicates and redundant information and writing different code for each catalogue is a pain.I have been toying with the idea of removing this 'main catalogue' and allowing my frontend to browse only the satellite databases, when users add stuff to their basket the neccessary information is added straight into an order table with no key back to the original data. There are obvious problems with both designs, but I quite like the idea of having my catalogues completely seperate so If i do need to maintain/reload them I can do and it will not affect my order fulfillment. disadvantage would be needing to write admin code to allow updates to each catalogue and to handle the fields differences in catalogues.The third option i guess is to take my seperate data sources and engineer them all into one big table, this presents problems keeping data up-to date and I still end up with redundant data (for example when products become deleted the rows would be marked unavailable but not physically deleted) not to mention the fact that the table size would be getting too much to be able to change things without affecting my users.its got me thinking but has anyone got somthing similar setup and can offer some advice or throw around any other ideas?ta! |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-04 : 18:39:49
|
| I would go with your third option. SQL Server can handle lots of rows. You can use indexes and stored procs and the like to speed it up. This will simplify all your code too, you only need to code it once.What it comes down to, is in a well designed database, you have one table for one type of thing. I.E. one table for "products". Once, you have multiple tables for "products" you are multiplying the amount of work you need to do.Damian |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-04 : 19:02:03
|
| The only time you would need the multiple tables is when you do horizontal partitioning of data.http://www.sqlteam.com/item.asp?ItemID=684Michael500 posts here I come!!<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
cDc
Starting Member
30 Posts |
Posted - 2002-11-05 : 03:11:05
|
| It would make sense i guess, the only problem I can see is that my satellite database are made up of many tables containing product information, some are 10gb in size, although the vital information for placing orders are contained in one table in each database (useful info like price, product name, supplier etc). So lets say I engineered all my product data into one table I would still need to link my products through to the related tables with a key - potentially I could use UPC barcodes but I have found them to not always work well, ideally I guess the primary keys to each database would need to be stored in the main table? Perfoming weekly and daily updates from CSV files to this main catalogue also would be troublesome. Thinking of it like this it doesn't sound like such a good solution plus all my eggs are in one basket so to speak if i need to reload an entire database from an original dataset and I mess up my whole system goes down while i sort it out whereas by having satellite databases I can bring one offline without affecting much. Any further thoughts/suggestions? Thanks! |
|
|
|
cDc
Starting Member
30 Posts |
Posted - 2002-11-06 : 16:52:17
|
| Let me try to put it a different way lets say Im selling sporting goods and I have 5 very differently structured databases which are given to me by suppliers - 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) i.e. searching and browsing is written to access those databases. the *essential* information is added into my order table - 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.I can see pros and cons of each but im a mere sql beginner but I am swayed towards this satellite designCheers |
|
|
|
|
|
|
|
|
DB Design Advice