If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How would I redesign my customers table?
I'm trying to get a handle on normalizing my database to better manage a
very large customer table. (1 million records...) My databases main function is managing direct mail campaigns and the call results from the mailing. The mail department gives me the names with the following information: IdString,ListCode,ListString,name,address1,address 2,city,state,zip,maildate EXAMPLE: GA502-100358,GA502,100358,Norm Petersen,123 main street,apt 3,boston,MA,90210,02/28/2005 IdString = the code they customer uses when they call in, "account number", primary key for customers table ListCode = Foreign Key to the Lists table ListString = a 6 digit number that is reset to 100001 each month, combined with ListCode to create IdString and always keep it as a 6 digit number I'd like to setup the customers table properly and effeciently so that I have a solid foundation to build on when we start incorporating the ordering process into this database. For reporting and maintenance I'm using Access 2002 "project" as a front end to SQL Server 2000 on a Windows Small Business Server 2003 Premium server pc. For the actual sales reps handling the incoming calls I'm using a web server program called "Resin" as a front end to the same SQL Server back end. I'm tracking call results for each list in the lists table, as well as stats on each employee listed in a employees table. Most of the call result info is stored in an orderinfo table that is essentially a 1-1 relationship with the customers table. The "Resin" guru is working on normalizing that portion of the project. Any help would be appreciated. |
#2
|
|||
|
|||
"OLOT" wrote in
m: I'm trying to get a handle on normalizing my database to better manage a very large customer table. (1 million records...) My databases main function is managing direct mail campaigns and the call results from the mailing. Okay. At the moment we have (at least) the following entities:- Targets Campaigns Mailings (this corresps to each envelope sent to each Target) ReturnCalls (presum relates to Target+Campaign, or to each Mailing) The mail department gives me the names with the following information: IdString,ListCode,ListString,name,address1,address 2,city,state,zip,mail date EXAMPLE: GA502-100358,GA502,100358,Norm Petersen,123 main street,apt 3,boston,MA,90210,02/28/2005 IdString = the code they customer uses when they call in, "account number", primary key for customers table ListCode = Foreign Key to the Lists table ListString = a 6 digit number that is reset to 100001 each month, combined with ListCode to create IdString and always keep it as a 6 digit number It's not quite clear how this relates to the situation given above. I.e:- - Do you try to maintain links between this Norm Petersen and the one you sent stuff to last month? - Is the ListCode a reference to Campaigns as you described it above? - Is the ListString actually the Target (customer) identifier? - Do you care if you have two different addresses for the same Norm Peterson? If you do, you'll have to do some parsing of this input file and some deciding about which address to use. Oh -- and how do you know if it is the same one or a different one? I'm tracking call results for each list in the lists table, as well as stats on each employee listed in a employees table. Most of the call result info is stored in an orderinfo table that is essentially a 1-1 relationship with the customers table. Good news - every target a customer: bad news - nobody orders more than once. If it's really one-to-one, why not just stick the stuff in one table? The "Resin" guru is working on normalizing that portion of the project. I don't think you can "normalise" part of a project. Either the thing is correct or it isn't. Hope it helps Tim F |
#3
|
|||
|
|||
thanks for the input. I've answered below inline...
"Tim Ferguson" wrote in message ... "OLOT" wrote in m: I'm trying to get a handle on normalizing my database to better manage a very large customer table. (1 million records...) My databases main function is managing direct mail campaigns and the call results from the mailing. Okay. At the moment we have (at least) the following entities:- Targets Campaigns Mailings (this corresps to each envelope sent to each Target) ReturnCalls (presum relates to Target+Campaign, or to each Mailing) targets = potential customers campaigns = each list that I mail mailings = mailing details returncalls = call center data how about: employees = sales reps The mail department gives me the names with the following information: IdString,ListCode,ListString,name,address1,address 2,city,state,zip,mail date EXAMPLE: GA502-100358,GA502,100358,Norm Petersen,123 main street,apt 3,boston,MA,90210,02/28/2005 IdString = the code they customer uses when they call in, "account number", primary key for customers table ListCode = Foreign Key to the Lists table ListString = a 6 digit number that is reset to 100001 each month, combined with ListCode to create IdString and always keep it as a 6 digit number It's not quite clear how this relates to the situation given above. I.e:- its not, other than this is how I currently dump it into my customers table - Do you try to maintain links between this Norm Petersen and the one you sent stuff to last month? The mailroom processes all duplications and the goal is not to mail the same person twice. (unless an entire campaign is rerun later on) - Is the ListCode a reference to Campaigns as you described it above? yes - Is the ListString actually the Target (customer) identifier? no, the IdString is the (customer) identifier, the ListString resets itself to the same value each month. Therefore, the ListCode and ListString combine to create the unique identifier. - Do you care if you have two different addresses for the same Norm Peterson? If you do, you'll have to do some parsing of this input file and some deciding about which address to use. Oh -- and how do you know if it is the same one or a different one? If a duplicate does slip thru, that (customer) will have to remain with multiple entries because we track which campaign they are responding to... I'm tracking call results for each list in the lists table, as well as stats on each employee listed in a employees table. Most of the call result info is stored in an orderinfo table that is essentially a 1-1 relationship with the customers table. Good news - every target a customer: bad news - nobody orders more than once. If it's really one-to-one, why not just stick the stuff in one table? This is how it is now, and the table is getting difficult to manage The "Resin" guru is working on normalizing that portion of the project. I don't think you can "normalise" part of a project. Either the thing is correct or it isn't. it isn't, and he's fixing it Hope it helps Tim F |
#4
|
|||
|
|||
"OLOT" wrote in
m: targets = potential customers campaigns = each list that I mail mailings = mailing details returncalls = call center data Quibble: "data" and "details" are not entities. You can have CallCentres, or you can have ReturnCalls (this is what you mean, I guess); and I think we already agree about Mailings. employees = sales reps You never mentioned anything about SalesReps, but yes, fine. IdString,ListCode,ListString,name,address1,address 2,city,state,zip, mail date EXAMPLE: GA502-100358,GA502,100358,Norm Petersen, 123 main street,apt 3,boston,MA,90210,02/28/2005 - Is the ListString actually the Target (customer) identifier? no, the IdString is the (customer) identifier, It can't be. For a start it's a compound of two other attributes, and therefore should not even exist in a table. To put it another way, having an "intelligent key" is a Really Really Bad Idea. For next, it cannot refer to a person (a Target or a PotentialCustomer) because part of it gets reset every month; and because the other part of refers to a Campaign, and that is going to change every time too. I can see that it (or, rather, the combination of the two other keys) would be a good Primary Key for the Mailings table, but it has nothing to do with identifying a person. and the table is getting difficult to manage A table cannot get any harder to manage than when it started, as long as it's designed right in the first place. A table of a million records is no harder to manage than one of ten. The thing that makes tables bad is having lots of fields, and you are not adding to them, surely? My guess is that you are getting lost because you have lots of non-dependent non- key attributes in this table because you have not decided whether it's a table of Mailings, of people, of campaigns, or of return calls. You are right: it cannot be all of them. They need a table each. HTH Tim F |
#5
|
|||
|
|||
IdString,ListCode,ListString,name,address1,address 2,city,state,zip,
mail date EXAMPLE: GA502-100358,GA502,100358,Norm Petersen, 123 main street,apt 3,boston,MA,90210,02/28/2005 - Is the ListString actually the Target (customer) identifier? no, the IdString is the (customer) identifier, It can't be. For a start it's a compound of two other attributes, and therefore should not even exist in a table. To put it another way, having an "intelligent key" is a Really Really Bad Idea. For next, it cannot refer to a person (a Target or a PotentialCustomer) because part of it gets reset every month; and because the other part of refers to a Campaign, and that is going to change every time too. What do you mean by "intelligent key"? Do you mean a field with more than one meaning, a primary key with any meaning, or something else entirely? I can see that it (or, rather, the combination of the two other keys) would be a good Primary Key for the Mailings table, but it has nothing to do with identifying a person. Let me see if I understand then: Targets primary key = autonumber (ie 1000587) Campaigns primary key = autonumber (ie 1257) Mailings primary key = (Campaigns PK + Targets PK) When we mail out our mailpiece, the (promo code) would be "12571000587", which would correctly identify the customer calling in and the campaign they responded to. and the table is getting difficult to manage A table cannot get any harder to manage than when it started, as long as it's designed right in the first place. A table of a million records is no harder to manage than one of ten. The thing that makes tables bad is having lots of fields, and you are not adding to them, surely? My guess is that you are getting lost because you have lots of non-dependent non- key attributes in this table because you have not decided whether it's a table of Mailings, of people, of campaigns, or of return calls. You are right: it cannot be all of them. They need a table each. bingo, and thats why I'm here trying to get it right. |
#6
|
|||
|
|||
"OLOT" wrote in
m: What do you mean by "intelligent key"? Do you mean a field with more than one meaning, a primary key with any meaning, or something else entirely? An intelligent key is one which embodies more than one piece of information in it -- it's a Bad Thing. I have to confess that I don't really know why it's called "intelligent". What I do know is that it makes all sorts of other joins and updates really hard and it is to Be Avoided At All Costs. A compound key (i.e. one key made up of a combination of two or more fields) serves the same purpose but means that the fields themselves can still be used properly. Targets primary key = autonumber (ie 1000587) Campaigns primary key = autonumber (ie 1257) Mailings primary key = (Campaigns PK + Targets PK) Yup: I'd buy all that. If you already have identifiers for the people or for the campaigns you (might be) able to use those rather than autonumbers, but it comes essentially to the same thing. When we mail out our mailpiece, the (promo code) would be "12571000587", which would correctly identify the customer calling in and the campaign they responded to. Yes. You can use this on a form or a report or in a query just by concatenating the value thus: txtPromoCode.ControlSource = _ Format(CampaignNumber,"0000") & Format(CustomerID, "0000000") or whatever. bingo, and that's why I'm here trying to get it right. I think that "kewl" is the word here..? :-) All the best Tim F |
#7
|
|||
|
|||
Targets primary key = autonumber (ie 1000587)
Campaigns primary key = autonumber (ie 1257) Mailings primary key = (Campaigns PK + Targets PK) Yup: I'd buy all that. If you already have identifiers for the people or for the campaigns you (might be) able to use those rather than autonumbers, but it comes essentially to the same thing. Campaigns primary key = text len5) "GU502","GU503",etc would be ok if in current use? Tim, thanks for all your help. |
#8
|
|||
|
|||
"OLOT" wrote in news:27oVd.10737$Pz7.5141
@newssvr13.news.prodigy.com: Yup: I'd buy all that. If you already have identifiers for the people or for the campaigns you (might be) able to use those rather than autonumbers, but it comes essentially to the same thing. Campaigns primary key = text len5) "GU502","GU503",etc would be ok if in current use? General rule for primary keys: they are constant: not subject to change, it's a real pain to update PKs when they are copied into related tables unique: _no chance_ of a duplicate, ever. known: no campaigns that don't have numbers, ever. At first sight, that would seem to be okay; but only you know the business. By the way, if the "GU" part is constant, you might prefer not to bother to store it, but just keep the number and put the GU part back on the forms, reports and letters etc. On the other hand, if the GU actually means something, then perhaps this is an intelligent key too? Tim, thanks for all your help. Glad to: it's what we come here for! All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 11:11 PM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 07:02 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |