CODEWITHSUNDEEP
phpmysqlsql
Vinayak Garg
Vinayak Garg

Reputation: 6606

File or Database for searching zip code?

I have a online shopping cart, at checkout user enters his zipcode.

There are 2 payment methods, cash-on-delivery and net-banking. The courier service ships to only certain areas(identified by zipcode). And the allowed list of zipcodes for COD and Net-Banking differ. (length of list = about 2500 for COD, and about 10,000 for latter)

Should I store these lists in database or a flat file?

For database, I will be querying using SELECT, and for file, I can read the entire(or partial) list in array, and then do Binary search on it.

Which one would be faster, considering following points -

  1. There is only one courier service now, but in future there will be more, and with different lists of there own. So I need to search in multiple lists.
  2. There is mostly read, write would be much less. Also the list should be customisable at later point.

I would have selected Database, but I don't know if it would make things slower, and I don't want to spend time designing database, when a file might be better.

EDIT: Say there are 2 courier companies ABC and DEF.
For file I will have 4 files (say) ABC_COD.txt, ABC_net.txt, DEF_COD.txt, DEF_net.txt. So if a customer goes for COD, I search ABC_COD, if not in there, I search DEF_COD and so on. So ok this seems to be costly, but it is also easily extensible.

Now consider database, I will have a table Allowed_zipcodes, with five columns : zipcode(int/varchar(6)), ABC_COD(boolean), ABC_net(boolean), DEF_COD(boolean), DEF_net(boolean). If the x company offers cod for y code, the corresponding column has true, otherwise false. While this seems good for lookup, adding a company involves a change in schema.

Please consider future changes and design as well.

Upvotes: 0

Views: 354

Answers (2)

stUrb
stUrb

Reputation: 6822

For some reason I think you should look at the magenta framework, isn't it already in some of the packages?

But if you want to do it yourself: Just to give you a starting point on the database model:

carrier
        id(int) | name (varchar)

zipcodes
        start(int) | end(int) | carrier(fk::carrier.id)

For instance:

carrier
        1 | UPS
        2 | fedex

zipcodes
        1000 | 1199 | 2
        1000 | 1099 | 1

Querying your zipcode and available carriers:

SELECT carrier.name 
FROM zipcodes 
LEFT JOIN carrier ON zip codes.carrier = carrier.id
WHERE
   zipcodes.end >= :code
AND
   zipcodes.start <= :code

Upvotes: 1

Manachi
Manachi

Reputation: 1064

Database, without any hint of a doubt. More logical, and more scalable.

Upvotes: 3

Related Questions