SQL Server Performance Forum – Threads Archive

Need Help with DB Structure

Hello
I need to develop a fast track application with limited time frame.
So inorder to save time and coding what i have did was combined similar structured tables into a single table and use a Category Code to differentiate records that supposed to go to seperate tables. i had 15 , 10 , 8 table sets that had same structure but each set differed by one or 2 fields and so i have combined all these 33 tables as single table so that single table support all fields and used a category code to differentiate the data. These tables are heavily used by the application and so i have some issue regarding data access. Now the application will access this only table for all process and what i want to know from you Great Experts is, what is the best and efficient way to create the tables Seperate Tables or Combined them into a single Table? For Programming purpose it is very easy to with Single Table but for database access etc i dont know how efficient this method is. Thanks for your help in advance with regards

Its hard to comment without exactly knowing what you are trying to model. Still, on the surface, it is not recommended to combine several tables into one table. Roji. P. Thomas
http://toponewithties.blogspot.com

So you think it is same like
having seperate Database for different application to
having all database combined to make a single database and all application access the one single table

I can’t imagine that you would save any development time by gluing tables together. the queries against those tables will be that much more complex and error-prone. did you factor in test and bug-fixing time in when you made this decision? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr noshade size="1">SqlSpec – a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org

Hello Jezemine
The Database Access Layer Class will be only one where we add delete update or fetch information from since it is only a single table. My presentation layer will present me the Category code which will help me identify which information I should retrieve from the table.
So there can be lot of generalisation done on my business logic layer and Database Access Layer and only my presentation layer changes. No of Stored Procedures and Views that i create also get reduced for i can reuse the same view or SP for my presentaion layer. I want to know how much costly this method can be becuase of application will always try to access mostly this single table.
So the stress this single table will face to distrubute the stress to different table is what i really want to know.
If my approach provides same kind of efficiency to that seperate table model then i can go for this model. Thanks for the help

The only way to know for sure if something will work or not is to test it. You have to put together some prototype code and try it. but my instinct and experience tells me that it’s a bad idea to store different entities in the same table. SqlSpec – a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org

A single table for everything is an invitation for deadlocks and excessive blocking. Roji. P. Thomas
http://toponewithties.blogspot.com

Thank you very much jezemine and Thomas.
So in your opinion Single table model though the structure of tables are same, is asking for trouble in terms of Deadlocks and blocking. The problem with testing is I dont have enough time to design the template and test it. So I will take your word and experience and go for Seperate Table Model. Thanks you very much and Best Regards

]]>