What are the advantages to converting to a multi-table design vs one h

[NSMan]

Our company currently provides several software systems that utilize SQL Server as a back end. Up to this point in time, our data structure has been stored in one huge table with generic field names (Field001, Field002, ect.). There is no way to make our software work with more fields.

Inside this table the record type is defined by an integer field that relates to the virtual table. (Customer = Type 5 records, Services = Type 6 records, ect) Linking between records is done through generic link fields and one identity field. Our database size ranges from a few hundred thousand records up to several million.

Thanks to new developments, we will soon, (hopefully), have the ability to utilize multiple tables. I am currently working on creating some benchmarks, but I was wondering if there might be any ideas and opinions among the group here as to what the positive and negative effects of switching from a one table design to a multi-table design would be. I've attached a list of our data structure on the one huge table we use.

Data_ID   int
Data_Type  int
Data_Link1  int
Data_Link2  int
Data_Link3  int
Data_Link4  int
Data_Link5  int
Data_Link6  int
LastEditedBy  varchar(5)
LastEditedDate  int
CreationDate  int
Locked   char(20)
Sfield001  varchar(50)
Sfield002  varchar(50)
Sfield003  varchar(50)
Sfield004  varchar(50)
Sfield005  varchar(50)
Sfield006  varchar(50)
Sfield007  varchar(50)
Sfield008  varchar(50)
Sfield009  varchar(50)
Sfield010  varchar(50)
Sfield011  varchar(50)
Sfield012  varchar(50)
Sfield013  varchar(50)
Sfield014  varchar(50)
Sfield015  varchar(50)
Sfield016  varchar(50)
Sfield017  varchar(50)
Sfield018  varchar(50)
Sfield019  varchar(50)
Sfield020  varchar(50)
Sfield021  varchar(50)
Sfield022  varchar(50)
Sfield023  varchar(50)
Sfield024  varchar(50)
Sfield025  varchar(50)
Sfield026  varchar(50)
Sfield027  varchar(50)
Sfield028  varchar(50)
Sfield029  varchar(50)
Sfield030  varchar(50)
Sfield031  varchar(50)
Sfield032  varchar(50)
Sfield033  varchar(50)
Sfield034  varchar(50)
Sfield035  varchar(50)
Sfield036  varchar(50)
Sfield037  varchar(50)
Sfield038  varchar(50)
Mfield01  varchar(100)
Mfield02  varchar(100)
Mfield03  varchar(100)
Mfield04  varchar(100)
Mfield05  varchar(100)
Mfield06  varchar(100)
Mfield07  varchar(100)
Mfield08  varchar(100)
Mfield09  varchar(100)
Mfield10  varchar(100)
Lfield01  varchar(250)
Lfield02  varchar(250)
Memo01   text
Memo02   text
Index01   varchar(50)
Index02   varchar(50)
Index03   varchar(50)
Index04   varchar(30)
Index05   varchar(30)
Index06   varchar(30)

 

[nice95gle]

NSMan, you might want to do some reading on the topic of ODBMS

Check these out.

http://www.objectivity.com/WhitePapers/WallPprIntro.shtml

http://www.odbms.org/index.html

We have implemented an application that utilized this logic but I am only a few weeks on, so the concept is still new to me.

 

[gmmastros]

There will be many advantages to switching to a multi-table design. Trust me.

One advantage is that you will be able to store more data. For example, I notice there are 10 Mfield's. With a multi-table design, you will have a virtually unlimited number of Mfield's because each one will be stored in a seperate record.

Another advantage will be performance. SQL Server stores data in 8K chunks. All but the text fields are stored in the 8K (the text fields store a 16 byte pointer to the actual data). So, by properly normalizing your data, you will be able to fit more records in an 8k chunk, so there will be less file i/o on the database. Since file i/o is slow, by reducung the file i/o performance will improve.

I beg you to learn as much as you can about database normalization techniques. I once heard... "Normalize until it hurts, denormalize until it works".

Do a google search on the following...

"Database normalization"
"referential integrity"
"foreign key"

There's a ton of information out there to help you create a properly designed database. This may be your only chance to 'get it right' because it is usually a time consuming process to change the design of a database. The only disadvantage of changing your existing database structure is that nearly every query will have to change. That probably represents a lot of work, but in the end will be worth it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Gooser]

Any time someone brings up Normalization, I have to throw this link out there; it's ingrained.

The Puppy Chart

------------------------------------------

Another advantage will be performance. SQL Server stores data in 8K chunks. All but the text fields are stored in the 8K (the text fields store a 16 byte pointer to the actual data). So, by properly normalizing your data, you will be able to fit more records in an 8k chunk, so there will be less file i/o on the database. Since file i/o is slow, by reducung the file i/o performance will improve.

...This is great info for my current argument with management about an in-development project!

I guess I had forgotten that tidbit.

Need to find a whitepaper or something on this.

Thanks,


v/r

Gooser

Why do today
that which may not need to be done tomorrow --me