Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 9th, 2009, 07:19 AM
Authorized User
 
Join Date: Apr 2009
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default What is the best way to split a 200 columns table

Hi,

I have a model that ended up in a 200 columns table. However , the modelled system can be logically broken down into subsystems ie A, B, C. So the question is, what is the best way of doing it? one-one relationship?

regards Lucky
  #2 (permalink)  
Old May 9th, 2009, 09:38 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Thumbs up Only if the split is clean

If the design breaks up naturally, then yes a one-to-one relationship might make sense. I've occasionally done this where there's one main table with a master ID. Then a set of related detail tables that use the same ID to link back to the master record. Sort of a one-to-one master/detail relationship.

If the data really break up logically, then you may be able to save some time shipping data back and forth between the application and the database. That's particularly handy if you have a network application so you're moving data across slower network links or if you have a huge number of records (saving a few K times several thousand records adds up).

But if the data doesn't really split apart naturally, then breaking up the table may be a mistake. A database can't help you optimize the data if you second guess its structure. At a minimum you'll create a bunch of primary key indexes that it will need to build and maintain.

A good test is to think about how the program will use the data. If you will always need only the data from one of the detail tables at a time, then the split is clean. If you will often need to get a piece of data here and there from several of the detail tables, then you've only made yourself a bunch of extra work putting everything back together before you use it.

In either case, you've given yourself extra work when you create and delete these records. Instead of a single delete, you need to also delete all the detail records. (Actually many databases can cascade the delete and do it for you but the database needs to perform that extra work.)

To summarize, if the table really splits naturally so you'll generally only need data from one sub-table at a time, you can split it in a one-to-one relationship. If the table doesn't split apart naturally, don't be afraid to have a big table. The database can probably handle it.

I hope that help. (Sorry for the long answer but someone else asked me this recently so I figure a detailed answer might help.)
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
  #3 (permalink)  
Old May 9th, 2009, 03:37 PM
Authorized User
 
Join Date: Apr 2009
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Many thanks for the answer. As to the ID in the master table and related tables being the same; is this ID configured as a primary key in each table i.e. master and related?

As to the break up it seems natural to me as each table models a subsystem and each subsystems is displayed on a separate web page. I then use a wizard to step through the pages.

So even though I need the data pretty much at the same time, I use separate web pages to display each subsystem.

regards

Last edited by luckystar; May 9th, 2009 at 03:38 PM.. Reason: clarification
  #4 (permalink)  
Old May 9th, 2009, 06:28 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Thumbs up That sounds reasonable

Yes, you would probably make an ID that is the primary key. Often it would be an arbitrary number like an ID number, account numbers, etc. rather than "real" data such as a name, address, or something.

Even though you're stepping through all the one page at a time, that spreads out the download time a bit so the user sees several smaller waits rather than one long one. Depending on how you're displaying the pages, you might even be able to pre-download some data from later pages and save the user time overall.

All in all, your approach sounds pretty reasonable to me.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
  #5 (permalink)  
Old May 9th, 2009, 06:30 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default Sorry, if I didn't make it clear

Sorry if I didn't make it clear, but the ID field would also be the primary key in the detail tables. You'll be joining on that key all the time so it deserves the performance given by the primary key. And there should be no more than one record with each ID value in each table so it can be the primary key.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
  #6 (permalink)  
Old May 10th, 2009, 04:43 PM
Authorized User
 
Join Date: Apr 2009
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi,

Many thanks for your reply; most helpful.

regards
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please give me and idea on how to split this table kennethjaysone HTML Code Clinic 0 December 3rd, 2007 05:37 AM
Sort Table Columns by Table Header Attribute omrieliav XSLT 4 June 7th, 2006 01:05 AM
Split up table across pages rabbit124 ASP.NET 1.0 and 1.1 Basics 0 April 11th, 2006 12:23 PM
generate XML from SQL server 200 table sasidhar79 SQL Server DTS 0 August 2nd, 2005 04:47 PM



All times are GMT -4. The time now is 01:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.