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 |
|
shauneee
Starting Member
3 Posts |
 Posted - 2009-04-08 : 07:07:02
|
| I have recently started in a web development company which specialises in holding data on many publications. As part of this data is a reqirement to store every word in a publication and it's location (a word map). These publications can be added and uploaded by our clients.Currently the desigin is to have a separate table containing the word map for each publication. i.e. the tables look something like this where the text <PUBID> is the publicaiton id from the publicaiotns table:CREATE TABLE [dbo].[tblWordLocations_<PUBID>]( [lPageFileId] [int] NULL, [lLeft] [int] NULL, [lTop] [int] NULL, [lBottom] [int] NULL, [lRight] [int] NULL, [zWord] [varchar](100) COLLATE Latin1_General_CI_AS NULL, [zLinkType] [varchar](50) COLLATE Latin1_General_CI_AS NULL, [lCharPos] [int] NULL) This produces many tables of the same structure and as the table name is different for each publication, the only way in interact with them is to use ugly dynamic sql in the stored procedures.I am assuming they have desigined it this way to make it scaleable, however would not a single table with a clustered index containing pub_Id not do just as good a job?If not where would the extra overhead come from and what are the pros and cons of each approach?N.B. The data is only ever accessed for one publication at a time and there are no cross publication searches on the word map data.Any advice would be greatly appreciated. |
|
|
shauneee
Starting Member
3 Posts |
Posted - 2009-04-08 : 07:13:43
|
| Sorry forgot to say:This is currently on SQL2000 and is being moved to SQL 2005. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-08 : 07:53:49
|
| Based on your description it would be better to add PubID as a column and store it all in a single table. The overhead would likely decrease, since data is stored as 8K pages, each table will likely waste a portion of a page and this waste would add up with multiple tables.And even though you don't need to support cross publication searches, putting it in one table will make it very easy to provide. |
|
|
|
shauneee
Starting Member
3 Posts |
Posted - 2009-04-08 : 09:22:01
|
| Thanks for the advice.I've also done a bit of research and, as we are moving to SQL2005, if the table grows too large we can use table partitioning to manage the large dataset without any code changes anyway. |
|
|
|
|
|
|
|
|
Dynamic multiple table or single indexed table