|
|
You're in the
sql server new users
group:One to One relationships
sql server new users:
First, you should realize that in SQL Server 2005 the limit on row
size is a bit more flexible when it comes to large varchar columns. But of course you are probably not running 2005. If I were doing what you propose I would probably keep the part that is common to all or nearly all clients in one table, a trimmed down version of the table you have now. Entries in the other tables would be optional, I would not create empty rows. The other tables would have the same key, SurveyID, as the main table, with a FK reference back to the main table. And I would define a view that performed LEFT OUTER JOINs to bring all the optional parts together with the mandatory part to look exactly like the all-in-one table you started with. In similar cases I have changed the name of the underlying table, and used the name of the original table as the name of the view. That allows existing code that reads the table to handle the view without modification. Updates, of course, must be changed. As to whether or not it is a good idea, that is a judgment call that has to be made by the person who deals with it every day. It is a valid approach to handle the type of data you describe, but I am not in a position to judge the particular case. Roy Harvey Beacon Falls, CT [quoted text, click to view] On Tue, 16 Jan 2007 10:32:04 -0000, "Vayse" <vvv> wrote:
>Hi >This is something I've often wondered about. In this case, I've been given a >SQL Server database that has details about a client survey. That is, an >agent goes to a client site and writes a review of that clients business. So >there is a clients table, linking to a survey table. There can be many >surveys for the client. >Each survey is broken down into sections. So some clients have >manufacturing, others have chemical processing, etc. >So this is a database I've inherited. Each section has a large variety range >of fields, mostly text fields of around size 500. >All the section info is stored in the one Survey table. I wish to break the >Survey table into several smaller table because > The table now has over 200 fields, which is getting that bit too awkward > New sections are required, which means the row size could get too large > Only a few sections are required by all clients, most sections are only >required by around 10% > >So I'm planning to create new tables - SectionA, SectionB SectionC etc. Make >SurveyID the primary key in each table. >Then create a one to one relationship between each Section table and the >Survey table, linking by SurveyID. > >However, I'm not sure that this is correct. Which raises three questions: >1) Does creating a relationship this way mean that each Survey must have a >corresponding entry in each Section table? >2) Is it better to create a new primary key in each Section table, then set >SurveyID as the foreign key? >3) In this case, I'm happy to break the Survey table up as not all sections >are required for each client. What if all sections were required, and >maximum row size was being approached. Is it ok to break up the table then? >Its not normalisation, more getting around practical limitations. > >Thanks >Vayse >
[quoted text, click to view]
On Tue, 16 Jan 2007 14:22:15 -0000, "Vayse" <vvv> wrote: >> Entries in the other tables would be optional, I would not create empty >> rows. The other tables would >> have the same key, SurveyID, as the main table, with a FK reference back >> to the main table. > >I'm not planning to create empty rows, but I do wonder why the relationship >would work. Just to be sure I'm not misunderstanding, this is what I have >done already: > Survey table, with primary key SurveyID > SectionA table, with primary key Section_SurveyID. > Link SurveyID to Section_SurveyID in my database diagram. > >As far as I'm aware, this creates a 1:1 relationship. But isn't this also >stating that for every Survey, there should be corresponding SectionA. >Or, is it actually creating a many to many relationship? What I would have: Survey, with a PK of SurveyID SectionA, with a PK of SurveyID, FK of SurveyID references Survey(SurveyID) I strive to use the same column name in all tables when it is the same data. The SurveyID in each table is the same SurveyID, so they get the same name. Roy Harvey
Hi
This is something I've often wondered about. In this case, I've been given a SQL Server database that has details about a client survey. That is, an agent goes to a client site and writes a review of that clients business. So there is a clients table, linking to a survey table. There can be many surveys for the client. Each survey is broken down into sections. So some clients have manufacturing, others have chemical processing, etc. So this is a database I've inherited. Each section has a large variety range of fields, mostly text fields of around size 500. All the section info is stored in the one Survey table. I wish to break the Survey table into several smaller table because The table now has over 200 fields, which is getting that bit too awkward New sections are required, which means the row size could get too large Only a few sections are required by all clients, most sections are only required by around 10% So I'm planning to create new tables - SectionA, SectionB SectionC etc. Make SurveyID the primary key in each table. Then create a one to one relationship between each Section table and the Survey table, linking by SurveyID. However, I'm not sure that this is correct. Which raises three questions: 1) Does creating a relationship this way mean that each Survey must have a corresponding entry in each Section table? 2) Is it better to create a new primary key in each Section table, then set SurveyID as the foreign key? 3) In this case, I'm happy to break the Survey table up as not all sections are required for each client. What if all sections were required, and maximum row size was being approached. Is it ok to break up the table then? Its not normalisation, more getting around practical limitations. Thanks Vayse
Thanks Roy. Its SQL 2000.
As you say, I'll be keeping the common parts in a trimmed down version of the Survey table. The ole 'give a view the table name' trick - I won't actually need that in this case, as the data entry and reporting is done on a section basis. [quoted text, click to view] > Entries in the other tables would be optional, I would not create empty > rows. The other tables would > have the same key, SurveyID, as the main table, with a FK reference back > to the main table. I'm not planning to create empty rows, but I do wonder why the relationship would work. Just to be sure I'm not misunderstanding, this is what I have done already: Survey table, with primary key SurveyID SectionA table, with primary key Section_SurveyID. Link SurveyID to Section_SurveyID in my database diagram. As far as I'm aware, this creates a 1:1 relationship. But isn't this also stating that for every Survey, there should be corresponding SectionA. Or, is it actually creating a many to many relationship? Vayse [quoted text, click to view] "Roy Harvey" <roy_harvey@snet.net> wrote in message news:dhkpq2lg0e1tf0jjo1or5mpb4ldpds5g04@4ax.com... > First, you should realize that in SQL Server 2005 the limit on row > size is a bit more flexible when it comes to large varchar columns. > But of course you are probably not running 2005. > > If I were doing what you propose I would probably keep the part that > is common to all or nearly all clients in one table, a trimmed down > version of the table you have now. Entries in the other tables would > be optional, I would not create empty rows. The other tables would > have the same key, SurveyID, as the main table, with a FK reference > back to the main table. > > And I would define a view that performed LEFT OUTER JOINs to bring all > the optional parts together with the mandatory part to look exactly > like the all-in-one table you started with. In similar cases I have > changed the name of the underlying table, and used the name of the > original table as the name of the view. That allows existing code > that reads the table to handle the view without modification. Updates, > of course, must be changed. > > As to whether or not it is a good idea, that is a judgment call that > has to be made by the person who deals with it every day. It is a > valid approach to handle the type of data you describe, but I am not > in a position to judge the particular case. > > Roy Harvey > Beacon Falls, CT > > On Tue, 16 Jan 2007 10:32:04 -0000, "Vayse" <vvv> wrote: > >>Hi >>This is something I've often wondered about. In this case, I've been given >>a >>SQL Server database that has details about a client survey. That is, an >>agent goes to a client site and writes a review of that clients business. >>So >>there is a clients table, linking to a survey table. There can be many >>surveys for the client. >>Each survey is broken down into sections. So some clients have >>manufacturing, others have chemical processing, etc. >>So this is a database I've inherited. Each section has a large variety >>range >>of fields, mostly text fields of around size 500. >>All the section info is stored in the one Survey table. I wish to break >>the >>Survey table into several smaller table because >> The table now has over 200 fields, which is getting that bit too >> awkward >> New sections are required, which means the row size could get too >> large >> Only a few sections are required by all clients, most sections are >> only >>required by around 10% >> >>So I'm planning to create new tables - SectionA, SectionB SectionC etc. >>Make >>SurveyID the primary key in each table. >>Then create a one to one relationship between each Section table and the >>Survey table, linking by SurveyID. >> >>However, I'm not sure that this is correct. Which raises three questions: >>1) Does creating a relationship this way mean that each Survey must have a >>corresponding entry in each Section table? >>2) Is it better to create a new primary key in each Section table, then >>set >>SurveyID as the foreign key? >>3) In this case, I'm happy to break the Survey table up as not all >>sections >>are required for each client. What if all sections were required, and >>maximum row size was being approached. Is it ok to break up the table >>then? >>Its not normalisation, more getting around practical limitations. >> >>Thanks >>Vayse >> >>
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |