1

I'm new here, so I'll keep my question straight and short. I am building a webapp that lets users select some product, submit their details and place an order.

Currently, my ORDER table contains only one field for the address. I was planning to write code to assimilate all address lines to one, including the state, city, etc. I will then add functions to retrieve the address field and parse it for state, city, etc.

Is this a good approach, or will the database be faster if there is seperate field for states, cities and the like?

3

Separate fields. Every time. String operations are costly, especially cutting them up into bits.

  • In addition, it allows you to use lookup tables of standardised address parts (like state, county, town and so on) which means you can improve the quality of your data by restricting the amount of free text rubbish users can enter. This is all in addition to being a much more efficient design. – Steve Pettifer Apr 4 '14 at 8:36
  • Will it be better if I save two fields as 1? For eg : if my app needs to search town & state very often, would combining them be better than combining all? – user21904 Apr 4 '14 at 8:43
  • No, absolutely not: That's what indexes are for. If you regularly search that combination then create a nonclustered index on those columns (possibly INCLUDEing the other columns your search will return UNLESS those columns are the ones which form the Primary Key). This is a decent introduction to indexes. And this link will get you started on design. Lots of other resources out there too, but don't make the mistake of concatenation! – Steve Pettifer Apr 4 '14 at 8:50
  • 1
    No worries, hope you find the links useful and if you have any more queries about design and indexing I'm cure the community will be only too happy to help steer you in the right direction! – Steve Pettifer Apr 4 '14 at 10:02
  • +1 Parsing free-form strings into structured addresses can be a special kind of torture. Avoid it whenvever possible! – FrustratedWithFormsDesigner Apr 4 '14 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.