.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Thoughts on table design?

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server

We are building a new OLTP database that has a design consideration that I am looking for feedback on. that consideration is tracking history of change on every row of every table in the design. CDC has been ruled out because its missing some feature that is needed.

My first thought for tracking history is to use triggers to capture changes to an audit table. however, the current design uses the stored procedures that insert/update rows to write to the audit tables. basically the current row is queried for and stored in a temp table...then a hash is created on the row...a hash is created on the new row and if they are different a row goes into the history table and the row is updated.

(i think this is bad design as the same code would have to be repeated for each procedure written that updates a table, and you have about 20-30 lines of code cluttering up every stored proc. for some reason some developers think triggers are evil though because they are "hidden")

the second thought on these history tables is that a row should be entered into the history table on insert of a row AND on update...the update should insert the new value.  I find that most people only want to store the old value of the changed record and not the new value.  the reason i would store the new value is so you dont have to look in 2 tables to find what a recor

View Complete Post

More Related Resource Links

Best table design for modelling a 1:0..1


Hi Gurus,


Apologies if this isn't the right place for this kind of question but I felt sure you guys would know the answer to this fairly simple design question.


I would like to model an item and a subject entity.  

An item can be assigned to zero or at most one subject.  

A subject may consume zero or one item.


How would it be best to model this kind of relationship in the database?  I was originally thinking of having an item FK in the subject table but that would potentially allow multiple subjects to be assigned the same item.  Then I was thinking of having a subject FK in the item table that would force only one subject to be assigned 1 item.

I think I'm just going round in circles here and it may be that my business logic should take care of these issues.  


My question is how is it best to model this kind of relationship?


Kind regards

How to create a Table of content in .rdl design in BIDS.



well am having a problem on.

How to create a Table of content in .rdl design in BIDS. either it should be dynamic or there should be bookmark of each section in TOC.

can any one help me out.?..



Help with design choice -- selecting parts for ordering table


The next part of what I'm working on will involve adding parts to an order, and will be more complex than probably anything I have done.

The amount of parts to choose from will be very extensive.

Anyone care to plant the seed,  have an example, or helpful input on easy ways to select the parts,  add them to a temp table/data table/data set, until all parts are selected (?), and insert into the database when done?

Dropdowns would, in cases, have a large amount of items. Select items from a gridview Master/Detail style? Checkbox lists?

I'm not looking for code, just a point in the right direction. I've looked at some shopping cart/e-commerce type designs, but it just doesn't fit what I'm trying to do very well.



How to create a Table of content in .rdl design in BIDS dynamically according to the Page no wise..?



well i am able to create the TOC statically by using the Document Map properties as describe in my older post issue 

but now i just want to create the Table of contents dynamically generate using the page no wise..is it possible...how can we do it while designing the .rdl in BIDS..



Updating the design of an SQL table


guys i would like to update the design of a table.

Trying to save the design i am getting an error that the operation isn't permitted.

A constraint exist between this table and another table

any idea ? thanks


Which table design is good for performance?


hi All,

I have situation to create table with datetime column for OLAP (more select stmt.) database with following conditions,

1.       Need to insert only date (no need time)

2.       Only SQL Server 2005 or less can be used.

3.       More than 10 million records capacity of the table.


use tempdb
drop table T1;
drop table T2;
create table T1(col1 int, colD tinyint, colM tinyint, colY smallint);
create table T2(col1 int, colDate datetime);
insert in

CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

Insufficient Permissions to alter table in SSMS Design View, can run ALTER Table successfully

Hello, are the permissions in the object explorer in management studio different for a login than what can be run in a query window for the same login? I have a developer I gave ddladmin so he can create/modify tables. He can open the table in design view in object explorer (gets a warning about possible insufficient permissions when he opens) but when he tries to add a column it fails with a permissions error. He scripted off the change and ran it in a query window, which worked fine. Any insight would be appreciated. Thanks.

SQL 2008 create table relationship design failure

i want to make three tables and relationship on design time. Cutomer has many orders and each order has many items. CustId is primary key for Customer table, OrderId and CustId are primary key for Order table, OrderDetId and OrderId are primary key for OrderDet table. Customer and Order tables has relationship one to many by CustId, Order and OrderDet tables has relationship one to many by OrderId. I can not make this on design time. gettting error on SQL 2008 "The Column in Order table do not match an exisintg Primary or Unique key constrains."

Abstract Factory Design in C# , Vb.NET

Provide an interface for creating families of related or dependent objects without specifying their concrete classes

Prototype Design Pattern in C#. Vb.NET

Specify the kind of objects to create using a prototypical instance, and create new objects by copying this prototype

Adapter Design Pattern in C#, VB.NET

Convert the interface of a class into another interface clients expect. Adapter lets classes work together that couldn't otherwise because of incompatible interfaces.

Singleton Design Pattern in C#, Vb.NET

Ensure a class has only one instance and provide a global point of access to it.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend