April 27, 2026 at 7:38 pm
I have an app that has an address field on the customer and the users. The address information on the users is informational and it is not used anyplace. The address used by the customer has only one purpose. It is one of the ways for the application administrator to determine that when they get a new customer that the customer doesn’t already exist in the application. My question is:
Should the address be stored as one long field or its component fields? I’m not even sure if the app is going to validate the address beyond not allowing nulls. That’s how little importance the address is.
Thanks
April 28, 2026 at 12:01 pm
You can literally do anything you want, any way you want to store information.
However, I always recommend that people think about reporting and searching. Is it possible, sure, you may not be using addresses today (by the way, five distinct individual people share my address, so you can't identify people by that address alone, might want to reassess whether or not that's a good choice for a unique constraint). What about tomorrow? Is it possible that you might want to search for all the people who live in 'Tulsa'? Wouldn't it be good if it was always spelled 'Tulsa' and wasn't sometimes just 'TUL', or typoed at 'Tlsa' or 'Tlusa'? Ah, then, having a lookup table for the city so that you always get the same spelling is a good idea. How about the state/province? Same idea. Postal codes? Probably worth validating. A big part of using a relational database system like SQL Server is taking advantage of what relational data can give you. Cleaner, more consistent data is better for reporting, better for AI, better for the overall usefulness of the data. So, yeah, I would absolutely build a good, thought out, relational structure to hold address information rather than dump it all in one uncontrolled column that can never be used for informational purposes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2026 at 7:07 pm
Thanks for getting back to me. I had considered the points you are making. And for those reasons I normally do as you suggested. This is the one application that I've worked on where the address has so little purpose. And since the app is going to be primarily used on a phone I was considering using one long field. That being said, I may switch to using the method that you suggest. It's just so tempting in this instance to use one long field as opposed to Address1, Address2, City, State and Zip.
Thanks again.
April 28, 2026 at 8:59 pm
I hear you. It is, in some ways, but not a lot, easier, but only in the short term. In the longer term, sooner or later, someone is going to ask to search by City/State/Whatever. Then you'll be happy you have it.
But again, nothing says you have to do it that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2026 at 3:45 am
just playing the contrarian...
one thing to keep in mind if you do ever have to validate any of the address stuff, it would be easier if it were broken up, but I guess you can use a web service to validate all that?
April 29, 2026 at 1:02 pm
Thanks for the advice.
May 21, 2026 at 8:16 am
My two penn'orth; if the address is in a single, unvalidated column, it would be virtually useless in determining whether the customer already exists in the database because any variation at all (eg. extra spaces between elements, or commas in one, but not the other) would result in the same real-life address appearing to be a new entry.
May 21, 2026 at 9:49 pm
Standard way most systems do this is:
I can't imagine there's not a good reason to do this as a default. You might add counties/area as free or a lookup, you might separate house number. There are reasons to do all these things, but I haven't seen a reason to drop all this in one field. A view or a concatenated field (computed column), can do this easily if you need it.
May 22, 2026 at 7:25 pm
My thoughts as well. Since my app is going to be primarily on a phone and the address isn't used for anything other than an FYI and to make an attempt at uniqueness, I wanted to save screen real estate. When push comes to shove, I expect that I will use the full address.
Thanks
May 22, 2026 at 7:46 pm
I hear you Steve. I have always used the full address. In the case of the app that I am building. It will primarily run on a phone. Therefore I am concerned about screen real estate. Plus in my case the address servers no real purpose other than as an FYI and a weak attempt to keep customers unique.
The other issue that I should have mentioned in my initial posts is that my customers are gated communities. As bizarre as it seems in gated communities a large of of them don't have a community address because mail isn't sent to the community. It's sent to the individual residents.
When I add a new customer/community I want to be sure that it's unique. That's part of why I asked the question. I am considering using the Community name, city state and zip as a unique key. In other words, dropping AddressLine1 and AddressLine2.
Thanks for your thoughts and help.
May 22, 2026 at 7:59 pm
I would then add address, address2, and address 3, populating the latter as the c/o <resident name>
That's how a lot of shared addresses work. For businesses, address 2 often is suite 100 or something. For mailing, a c/o works well.
I get saving realestate. I'd just make this easy. If osmeone logs in, prepopulate as one text field, but edit as separates.
May 26, 2026 at 12:59 pm
Just never forget, downstream, in the future, you, or someone, is going to want to report on this data, slice and dice it. The more you make that easy now, the more you'll be happy in the future.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply