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 |
|
Phloxicon
Starting Member
2 Posts |
 Posted - 2008-08-03 : 10:45:18
|
| I'm coming from a C++ background and I've dealt with some files using fstream etc. lots of times. Now I'm giving SQL a shot and something is bothering me.Surely it's faster to have lots of small tables with a bit of information rather than few tables storing massive amounts of information.For example, imagine there's an e-commerce site like Amazon. It could have just one table called PurchasedItems that has the foreign key of the customer who purchased the item and the foreign key of the item they bought. The table could then get all of the items a person bought using the query "bla bla blah, FROM Purchases WHERE CustomerID = @CustomerID". This would work fine but wouldn't the entire table then have to be searched, making the process take longer?Alternatively, couldn't a new table be created for each customer so the search would only check a table in which all of the entries are for that customer? The search would be something like "blah blah FROM @ParticularCustomerPurchasesTable WHERE CustomerID = @CustomerID".I really don't know if I'm right here because I'm new to SQL and I don't know if it has been optimised in some way to perform better if it's just one big table that stores all entries. Is there something I'm overlooking? Is there any reason not to have two or three tables per customer? Would it actually be faster and more efficient?Thanks for all replies :) |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-03 : 10:51:52
|
one table per customer is not a good design. millions of tables would not perform well, and would be very very very hard to manage.if you have hundreds of millions or billions of rows, you can partition the table horizontally but logically it's still one table. physically a partitioned table may be spread across several disks.if you have to scan the whole table to find the purchases made by a customer, then I'd say you forgot to add the proper indexes to the PurchasedItems table. 
elsasoft.org |
 |
|
|
Phloxicon
Starting Member
2 Posts |
Posted - 2008-08-03 : 15:46:15
|
| Oh, cool. Thanks for the advice. I'll definitely look into this.Thank you very much :) |
|
|
|
|
|
|
Wouldn't it be more efficient to have more tables?