Picklists? What's the best method - XML or Database?

Question

User1100692814 posted

Hi

I need to design a few number of picklists for a new application at work, that also need to accomodate multiple languages.

What would be the best way to do this? Database tables for XML?

Example: A service "Task" has a "Task Type" i.e. Phone Call, Email, Appointment etc... Do I create a table called TaskType or do I create a XML file called TaskType, but my concern is then with reporting? If it was all db related I could simply do joins and sort my issue but my concern is that by adding database tables for simple picklists adds an additional +- 50 tables to our database.

Any help our pointers would be great thank.

Regards

David

<PickList>
   <TaskType Locale="en">
      <Name Id="1">Phone Call</Name>
      <Name Id="2">Email</Name>
      <Name Id="3">Appointment</Name>
   </TaskType>
   <TaskType Locale="fr">
      <Name Id="1">Appel Téléphonique</Name>
      <Name Id="2">Email</Name>
      <Name Id="3">Nomination</Name>
   </TaskType>
   <TaskType Locale="esp">
      <Name Id="1">Llamada Telefónica</Name>
      <Name Id="2">E-mail</Name>
      <Name Id="3">Cita</Name>
   </TaskType>
</PickList>

Answer 1

User-417784260 posted

If you need it for reporting I would recommend using the database tables.  I also think it is easier to add or change an item in the drop downlist if you are using a database table instead of xml

Answer 2

User-952121411 posted

If it was all db related I could simply do joins and sort my issue but my concern is that by adding database tables for simple picklists adds an additional +- 50 tables to our database.

These 'picklists' or 'lookup' tables as they are called are common place in a database. Adding the tables needed to support them is acceptable IMO. You really are not going to have any significant overhead adding 50 tables to SQL Server; it is made to handle this load and much, much more. Also by storing the data in a database you have the full power of SQL Server and TSQL to work with the data.

Using XML is typically an option I would explore if database access was restricted, or some sort of disconnected environment where the SQL database could not be reached. However even in a disconnected environment you could still use a SQL Compact Database as an alternative.