Hi,<br><br>I have a table<br><br>tblCustomer<br> CustomerID<br> LastName<br> FirstName<br> Company<br> Phone<br> Fax<br> MailingAdd<br> MailingAdd2 <br> MalingCity<br> MailingState<br> MailingZipCode<br> PremiseAdd<br> PremiseCity<br> PremiseState<br> PremiseZipCode<br><br>There is always a Mailing Address and a Premise Address... should I put these in seperate tables to normalize them and just make foreign keys?<br><br>Also...I've been looking at the back-ends of a couple of Access Accounting Databases and don't understand why they seem to put so much in the Customer table...they put discount percentage, Interest, Current Balance (yes s/b calculated field but store it)...etc.<br>Can anyone explain why the back-ends of receivable management systems aren't normalized? Is it a performance issue?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
normalization?
- Thread starter advpay
- Start date
I would say there are no repeating groups in your table, and no violation of 1NF. Having more than one mailing address is common, and they represent different information. However in some situations, you might encounter situations where you need even more addresses, like different (perhaps temporary) holliday adress, for some customers maybe home and office addresses etc. When such a situation arise, and there isn't a predetermined number of addresses you want to store, I would consider it a 1NF violation, and normalize.<br><br>I’m not very familiar with the US Zip-codes, but if it resembles the system where I come from, one zip-code determine the City. If that’s the case in US to, I would say this might be considered to be a 3NF violation.<br><br>Your question about why backend-systems, there might be a number of reasons, but my number one, is always incompetence;-)<br><br>One reason for storing calculated fields is performance, another good reason is that the values, used to calculate the field might change. Let's say you order 2 books, at a price of 10$. Between order and delivery the price changes to 11$. If you don't store Your price, sum balance etc, your bill will show 22$. In a lot of systems, calculated fields and derived values are stored to keep history, and to ensure that the values stored for each customer is exactly what was agreed upon.<br><br>Any others have opinions?<br>
- Thread starter
- #3
Thanks...this is considered a leading software companies Customer table that I was talking about...<br><br>tblCustomer<br> Customer Number<br> Active<br> Customer Record Type<br> Type of Customer<br> Region<br> Salutation<br> First Name<br> Middle Initial<br> Last Name<br> Company<br> Street<br> Suite<br> City<br> State<br> Zip/Postal Code<br> Country<br> Phone<br> Fax<br> Other Phone<br> Email<br> Tax Exempt<br> Sales Tax Code<br> Sales Tax2 Code<br> Discount Percent<br> Markup Percent<br> Credit Balance<br> Pricing Type<br> Code<br> Comments<br> Payment Terms<br> CreditHold<br> Salesman<br> Shipped Via<br> Route Delivery Code<br> Route Delivery Sequence<br> Route Delivery Day<br> Finance Charges<br> Last Finance Charge Date<br> Finance Charge Acct<br> Finance Charge Pct<br> Bill To Customer Number<br> Current Balance<br><br><br><br><br><br><br><br>
The funny thing is, I've seen a lot worse;-) - but to me, this is a case of un-normalized design which I wouldn't present any of my clients.<br><br>But there are always reasons. You'r excample here, probably comes from a "standard system", where it's essential to meet a lot of different requirments (the client/user might want to add this and that information...), but in my opinion, thats not an excuse for placing it all in one table.<br><br>Normalizing is something you do "with paper&pencil;-)", and includes the excact same steps without any consideration regarding the DBMS on which it's going to be implemented.<br><br>Saying that, I'd still like to comment on Access in a so called "Client/Server" environment. Using the "standard" Access Jet Engine does not fully qualify regarding the term "Client/Server". One of the reasons being that it doesn't process any data on the "Server"-side. So even if you limit the number of records and fields through queries, all the data has to travel over the network from the "Server" to the "Client" to be processed (and finally displaying a small recordset). Even empty fields use network capacity, so... <br><br>Would be nice to have someone else to comment;-)<br><br>Roy-Vidar
advpay, are you talking about normalizing tables or optimizing their design? On the zip code, yeah, if you had a current lookup table for every zip in the US, you wouldn't have to key it, but that zip table might be large & expensive and need frequent updating, so it would be valid to just collect the data from the customer and store it instead. <br><br>On a lot of those other fields, I'm not sure exactly what they hold, but it <i>could</i> be customer-specific data, and if so then it would be normalized. If every data field is atomic and relates to "the PK, the whole PK, and nothing but the PK, so help me Codd", most normalization concerns are addressed.<br><br>I agree with Roy-Vidar that it's a lot of fields for one table for a file-server rather than client-server setup. You might put the customer-finance fields in one table, customer-demographics in another, etc. if you were concerned with minimizing network traffic. But I think you'd call that a performance issue, not a normalization issue. Optimizing table design goes beyond normalizing, and there are lots of other considerations like how the app itself works. I'm slowly learning to <i>try</i> to avoid criticizing anything that works. 
- Thread starter
- #6
Elizabeth & Roy,<br><br>Thanks for your input...I am going back and forth on my design issue; how it will effect storage and its performance over the network. I have also been some what stuck with my decisions on my design ...I have asked several DBAs, Instructors read books on Accounting Design with relational databases....all the questions I have asked get answered differently (and I expect some variance) depending on who I talk to. Some are telling me normalize, normalize, normalize...and others say there must be a reason they do what they do... every back end that I look at (accounting) isn't close to being normalized and wastes tons of space. I understand that its great that all these companies have a system up and running and I am not putting them down just want to understand why their accounting database designs are most efficient...and its not clicking.
If you're really serious about design you might look into books on that subject that talk about resusable design patterns in software. I keep meaning to get something on this myself, among the many other things I keep meaning to do. One that was recommended to me is <A HREF=" TARGET="_new"> but I haven't read it myself.
- Status
Similar threads
