0

I'm creating a list of all countries and major cities in the world. The list has about 7000 nodes including countries, states and cities. It seems like this:

United States - California - Los Angeles

And I need to search locations by keyword. I've created a XML file to store them, the file is about 300kb and average query time is 30-40ms.

But now I need to search locations by multiple languages, so I must extend the file which is a problem. I like the convenience of reading XML directly but I need the extensibility of database.

I wonder which one I should use to store data like these that don't change often but need to be queried?

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
Qyy
  • 33
  • 8
  • SQLite seems like a good choice for a small database. You can produce human readable reports from it. – Schwern Apr 11 '21 at 05:24
  • Sorry, but voting to close: StackOverflow does not encourage open-ended application architecture questions. I was tempted to respond discussing whether your problem was extensibility or performance, but that kind of response is not an answer to a question, and that's exactly why it doesn't fit the StackOverflow model. – Michael Kay Apr 11 '21 at 17:25

2 Answers2

0

XML is a very useful technology for moving data between different databases or between databases and other programs. However, it is not itself a database. XML is a data interchange format. One can have XML parsing libraries that can query a DOM with XPath expressions but that is not the same thing as a DBMS.

I would prefer to use any DBMS over XML.

Here I have found another stackoverflow question regarding this which has good answer:

“XML is not a database. It was never meant to be a database. It is never going to be a database. Relational databases are proven technology with more than 20 years of implementation experience. They are solid, stable, useful products. They are not going away. XML is a very useful technology for moving data between different databases or between databases and other programs. However, it is not itself a database. Don't use it like one.“

https://stackoverflow.com/questions/201568/when-would-i-use-xml-instead-of-sql#:~:text=XML%20is%20a%20very%20useful,is%20not%20itself%20a%20database.&text=XML%20is%20a%20data%20interchange,same%20thing%20as%20a%20DBMS.

You can create three tables:

Country (ID int, Name varchar(200));
State (ID int, Name varchar(200), CountryID int);
City (ID int, Name varchar(200), StateID int);

ID field of all three tables can be auto incremented primary key column. CountryID in State table will be foreign key to ID column in Country table and StateID in city table will be foreign key to ID column of State table.

You can also add code column to all three tables to add country code, state code and city code to you data.

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
0

You Can create a single table and store all the data in same table base on Parent Id.

CountryInfo(Id int,Name varchar(200),ParentId int)

1-India-null
2-Delhi-1
3-East Delhi-2
4-Central Delhi-2
5-UP-1
6-Noida-5

Dhananjay Kumar
  • 1