| Karla Carter's profileKarla's Coding and Stuff...BlogLists |  Tools  Help |
|
|
March 15 Article: Ten of the Biggest Mistakes Developers Make With DatabasesI've heard tales of overly normalized databases where the official US State abbreviations were in a lookup table... October 02 Another Opinion on Look-Up TablesRecommends use of a generic lookup table, with bonuses of performance, maintainability and centralization.
September 01 "Is it better to build one giant lookup table or to add a child table for each table requiring data?"Printer-friendly version of an April 2002 Advisor.com article
Database Developer's Dilemma
Is it better to build one giant lookup table or to add a child table for each table requiring data?
By Richard Campbell and Mike Groh, Technical Editors of Access-VB-SQL Advisor Magazine I'm in the process of building a new SQL Server database for a client, and I've encountered one of those hard-to-answer questions we all have to deal with from time to time. This database has to track customers, and each customer may have multiple locations, so the database has to handle an arbitrary number of addresses for each customer. In turn, my client's employees and vendors each have addresses. In every case, the general format for the address data is the same: StreetAddress, City, State, and Zip.
Because there is an arbitrary number of addresses for each entry, I'll have to build child tables to hold the address data. The dilemma is whether it's better to build one giant address lookup table, or to add a child table for each table requiring address data. Consider this: There may eventually be six or seven different parent tables (customer locations, employees, vendors, etc.) requiring address data. Building address tables for these tables will add another six or seven tables to the database schema, and complicate the queries selecting address data for the entries in those tables. The situation is worse when you consider that the database needs child tables for e-mail addresses, phone numbers, Web site addresses, and a host of other data in the application. It would simplify the database schema to add a single address lookup, adding a field that identified the source (customer, employee, vendor) of the address. Queries would be correspondingly simpler as well. It turns out there are no hard-and-fast rules governing the choice between single lookup table and multiple child tables. I posed this question to Technical Editor Ken Getz, Contributing Editor Andy Baron, and Tom Howe the best database design experts I know. Ken replies: "I wouldn't even consider having separate tables. I'm sure there are normalization rules and theories about this, but it sure seems to make sense to me. It's all the same type of data. You can have a lookup field indicating which address is which type of address." Tom checked in with "In most of my applications, I use table structures that include entity tables such as tblClient, tblEmployee, tblContact and many more. There are secondary tables such as tblAddress, tblEmail, and tblPhone that include all of the addresses, e-mail addresses, and phone numbers for ALL types of people. Each secondary table (e.g., tblPhone) has a Type table (tblPhoneType), so phone numbers can be categorized as Home, Work, Fax, Cell (...). Andy, on the other hand, prefers a more formal approach. He suggests: "I tend to normalize everything, which means lots of tables and lots of joins. So, I usually have a Persons table; a Companies table; tables to associate persons with companies; tables for physical addresses; and tables for phones, phone types, electronic addresses, and types (and lately I think I'd have a table for contact modalities that included electronic types and phone types as variations). If people contractors and company contractors have some things in common and some things different, I might have a Contractors table and separate PeopleContractors and CompanyContractors tables (with ContractorID and CompanyID in the CompanyContractors table). Often, I have a table for CompanyDivisions and CompanyDivision-Addresses (linked to the Addresses table)." Andy had an interesting observation. "I find this normalization process of separating what's the same from what's different to be logical and satisfying. I feel like the data design is capturing important information about how the problem set is organized. It's really very close to object-oriented programming at the data level." What Andy is saying is that he likes database tables to represent the entities they describe, even if it means a more complicated database design. When you look at a database diagram, tables such as PeopleContractors and Company-Contractors clearly indicate the data they contain, even if the table designs are identical. The bottom line is it's the developer's choice which design to use. Some people prefer the simplicity of a single lookup table, while others want a more granular approach. I finally decided in this database to put multiple smaller tables into the design. I decided that, because I'm in the initial phase of the database's design, it's hard to know how the data will eventually be used in the application. For instance, it's possible my client will want to add a special field to the CustomerAddress table indicating shipping instructions. Because this field is not relevant to the other address types, it would be extra baggage in a large addresses lookup table. Perhaps at some point, I'll consolidate the small child tables into a single lookup table, but in the meantime, I'll just deal with the complexity of the database's schema and queries. At this point in this project's development cycle, it's the only solution that makes sense. August 09 Reflections on Access and Using it as a Web DatabasePoor Access.
It's so completely not cool.
I remember back 10 years ago when I first went over to the dark side of Windows programming (my first real programming was on an HP3000 minicomputer doing SPL and C CATI coding for a market research company in Chicago). I was motivated by greed, actually. Well, not greed, exactly, because I'm really not a greedy person. OK, except when it comes to things like pizza...
So, I was in my second market research job at that point, having left the CATI job (loved the work I was doing, boss was great, politics and pay I hated) for supposedly project-related work where I actually ended up being a Macintosh admin and DOS coder, and somewhat of a FileMaker Pro guru. (Yes, I had trouble sleeping at night...) I was 30 and feeling pretty sorry for myself at the state of my career.
Bring on the want ads!
I kept seeing ads for Access 2.0 developers. They were offering great salaries. The trouble was 1) I was working in a Mac environment and Access 2.0 didn't run on Mac 2) the ads kept mentioning certification. I didn't have that, either. At that time I seemed to enjoy developing for desktop databases and Access looked to be the logical next step. And there was that money thing - had Access jobs not come with the promise of such reward I don't know that I would have pursued it. Anyhow, I ended up selling my soul/becoming an indentured servant to a mass market technical training company to learn Access and get my certification (that's a whole other story that I'm sure you'll be tormented with at some later point ;-) I am not only certified in Access 2.0, but also Access 95. (Wheee, sounds of cheering and applause). At one point I even made decent amounts of money :-D (Ah, the halcyon days of the late 90s...)
As I became geekier (yes, that is possible - there are many, many levels of geek that I have yet to achieve and considering that I don't really care for computer games or Nintendo/XBox/Playstation many of those levels will remain eternally beyond my grasp...) I learned SQL Server, considered to be a real database. (Although I'm certified in SQL 2000 development, I consider myself only slightly dangerous and would never call myself even skilled at it, i.e. I won't teach SQL Server classes.) I can spout the party line about how Access is a toy database (quote from a friend and fellow developer upon telling him of this entry: "but it doesn't matter cuz real developers don't use Access; sure, encourage them to use Access, just not as a database") and SQL Server is the be-all and end-all of relational databases.
Still, I feel bad for Access. And I know of lots of folks who have no choice but to run their Web apps against it. Even me: my Brinkster accounts come with free Access databases (yes, I could pay extra for SQL Server but I needed the pizza money...) - it's helpful to know how to program for the dreaded .mdb file.
Now, places like Brinkster will have special database directories set up for the users with write privileges. However, the home developer (or office developer) attempting to use Access for Web site development often runs into permission issues. Basically put, it has to do with the .ldb file and the fact that the ASP.NET user account does not natively have write permissions. Or, as it's put in an MSDN article, "The ASP.NET user, by default, does not have permission to write a record to a database or create a locking file (.ldb) in the folder containing the database. You must give the ASP.NET user these permissions....You can add ASP.NET write permission to both the database file and the folder that contains it." It's an easy enough feature to overcome.
Some of the examples on karla.geekesses.net make use of an Access database. Please feel free to look over the code and use it for yourself (check out the Creative Commons licensing first :-)
Just don't tell your really geeky coworkers that you're working with Access... ;-) August 08 MSDN Magazine Article: Stop SQL Injection Attacks Before They Stop You"The basic idea behind a SQL injection attack is this: you create a Web page that allows the user to enter text into a textbox that will be used to execute a query against a database. A hacker enters a malformed SQL statement into the textbox that changes the nature of the query so that it can be used to break into, alter, or damage the back-end database....All it takes is an application with some dynamically constructed SQL and untrusted user input. Given the right conditions, the extent of damage caused by such an attack may be limited only by the extent of the hacker's knowledge of the SQL language and the database configuration." Managing an @@IDENTITY Crisis"Discusses a strategy for returning newly created Identity values after the INSERT is executed. Letting SQL Server generate Identity values might make your job harder if you don't know how to retrieve the new Identity values." July 22 Great Series on Database Worst PracticesOne of my fellow MCTs, Neil MacMurchy, a DBA in Canada, introduced me to SQLServerCentral.com. If you get a free membership they give you a virtual briefcase to store articles and scripts in - very handy. The site, of course, also generates seemingly daily email (you can turn this off if you want) and today's bulletin included a mention of a great series: Worst Practices.
There are four articles thus far:
|
|
|
