where to keep data

One design question.

I need to use database in my application. It�s just one table with 7-8 columns and 60-70 rows. I think going for an XML is not a good idea as editing would be tough. So two options are coming in my mind: any database or excel sheet (with POI API).

Which would be more efficient? Database calls are more costly or POI calls?

(Data will be loaded once in the beginning, and there will be no editing from the application, but it can come as a future requirement).

Thanks.

As you know that modification of data would be part of future requirement, you can consider using Databases.

POI - its good but i am not too sure of its capabilities (means, to what extent it can serve you). It again depends on the depth or level of the modification of data.

If its gonna be plain retrieval and update, you can go for POI otherwise better you can prefer Databases.

There are, of course, plenty of other ways to store a simple table of rows and columns. These include, for example:

a text file in something such as CSV or TSV

a properties file where the property names encode column and row (such as name_1 = Frank)

something like a hashMap in memory, saved/loaded using Java serialization

an XML alternative such as JSON or YAML

... and no doubt many more.

All of the above offer a way of loading, modifying and saving some modest data.

From this wide choice, I recommend you first do two things:

define a storage interface which is independent of the actual storage mechanism and format, and write your application code in terms of that interface.

write the simplest possible implementation of that interface, and use it while developing your application.

Then, once your application works with your ultra-simple storage mechanism, you can look at how (and, indeed, whether) you need to code another implementation which uses some other storage method.

Does that make sense?

Originally posted by Frank Carver:

Does that make sense?

It does make a lot of sense.

a text file in something such as CSV or TSV
a properties file where the property names encode column and row (such as name_1 = Frank)

I didn't think XML as an option as it's not convenient to edit the data for non-developer. I think same true for text file and properties file too.

something like a hashMap in memory, saved/loaded using Java serialization

I quite didn't understand this. HashMap etc are okay but where the data will be (at least the first time)?

an XML alternative such as JSON or YAML

Not convenient to edit, I guess.

I am keeping this "storage functionality" apart from rest of the functionality as you said. So I can switch to different storage anytime.

Thanks.

as it's not convenient to edit the data for non-developer

Ah. Do you anticipate that non-developers will need to edit this datafile then?

From your description above I inferred that the data file would be created once then loaded and used many times by the application, so the choice of format was driven mainly by simple and robust coding. If you have other requirements of this format, then maybe we need to explore the range of choices a bit further.

Can you describe in a bit more detail how you expect the stored form of the data will be created and used during normal work processes.

Originally posted by Frank Carver:
as it's not convenient to edit the data for non-developer

Ah. Do you anticipate that non-developers will need to edit this datafile then?

From your description above I inferred that the data file would be created once then loaded and used many times by the application, so the choice of format was driven mainly by simple and robust coding. If you have other requirements of this format, then maybe we need to explore the range of choices a bit further.

Can you describe in a bit more detail how you expect the stored form of the data will be created and used during normal work processes.

Sure Frank.

At present it's an excel sheet of employees. One row for each employee and 7-8 columns for each attribute of employee.

(Since it's an excel sheet at present, so idea of 'excel and POI' came into my mind, but that's not a big deal to transfer data to any other storage with a program)

Now as I mentioned, there will be no editing from application (or from GUI). But user will do need to edit data, but on their own (without any GUI).

So if I keep data in XML, text file or properties file then it might hurt user to edit the data.

If I keep data in excel sheet only then editing would be, much, much easy for user as everyone know excel sheets. But how good it is from efficiency point of view?

If I keep data in any database then also editing would be tough, but I get advantage of SQL for data retrieval.

So my only doubt is, is using excel too harmful for efficiency and can it kill the application's speed??? If yes, then I will go for other mentioned option and provide GUI for editing.

Thanks a lot for your time.

[ May 23, 2007: Message edited by: ankur rathi ]

Originally posted by ankur rathi:

If I keep data in excel sheet only then editing would be, much, much easy for user as everyone know excel sheets. But how good it is from efficiency point of view?

[ May 23, 2007: Message edited by: ankur rathi ]

There... you have your answer

Design to suit the users requirement at the same time taking care of the efficency and performance of the service.

Let the user work on Excel, save as CSV file, your application picks up data and stores it in a object structure for the applications use henceforth.

User is happy => Excel is a standard amongst business users and most of them are comfortable with the same

Application is Happy => it gets to work on a object structure

You can build Queries on the object graph to return data as required, one drawback is RAM usage and the above suggestion should be benchmarked against the memory footprint.

In general I agree with Devesh. Give the users an interface which suits them best,and do the rest in software.

However, I stil think you may be missing something here. You write: If I keep data in excel sheet only then editing would be, much, much easy for user as everyone know excel sheets. But how good it is from efficiency point of view?

If I keep data in any database then also editing would be tough, but I get advantage of SQL for data retrieval.

So my only doubt is, is using excel too harmful for efficiency and can it kill the application's speed??? If yes, then I will go for other mentioned option and provide GUI for editing.

The key thing I notice from your description is the workflow. It seems that anyone (or at least anyone authorized) can change and re-save the Excel file at any time. So you are very likely to need some code to look at the saved file and decide if it has changed since you last used it.

If that is the case, there is no reason why your application can not transfer the information from an Excel file to something more efficient for your processing whenever it notices that the file has changed. Then you can have the usability benefits of Excel, and the performance and easy coding benefits of a database or an in-memory table.

I'm still slightly puzzled as to why you are not considering building an editing UI. Even if you don't fancy a Java solution, then why not a simple Access application. An app to provide friendly editing of a single DB table shouldn't take more than half an hour to set up.

Or am I still missing the point?

You've hit a couple Excel solutions, and they both sound fine. And familiar.

I build my web site SurfScranton from an Excel spreadsheet. A macro saves normally, saves as tab delimited, launches a bat file that does the build and reopens the normal XLS. Save as tab-delimited leaves it in the new tab-delimited file, hence the re-open.

I recently used POI to read a spreadsheet and generate a Wiki table. The POI interface is pretty slick, but it took a fair bit of effort to format the various data types to look more or less like Excel.

As pointed out, Excel is not great in a multi-user environment, say sitting on a file share. If that gets into trouble, some of the fancier solutions suggested above may be required.

As you might now, POI stands for Poor Obfuscation Implementation. It's your way to hack from Java into the filthy MS-Office docs. Even the guys from Apache POI are talking, if you read their API's, about things like HSSF, which stands for Horrible Spreadsheet Format or about HWPF which stands for Horrible Word Processing Format.

Conclusion: newer use POI as a database stategy, it will be cenrtainly less efficient than setting up your own db.

Originally posted by Frank Carver:

The key thing I notice from your description is the workflow. It seems that anyone (or at least anyone authorized) can change and re-save the Excel file at any time.

Well, only admin will use this software and so only he/she would require to edit the data, that too very rare.

I was assuming that whenever admin will change the excel file, he/she will restart the server as the data is loaded once on startup. But that will be definitely not a good idea...

But having code to look for change in file and if change found then load data again, seems complex to me.

If that is the case, there is no reason why your application can not transfer the information from an Excel file to something more efficient for your processing whenever it notices that the file has changed. Then you can have the usability benefits of Excel, and the performance and easy coding benefits of a database or an in-memory table.

Of course, I will load the data in objects and use them trough out my application.

I'm still slightly puzzled as to why you are not considering building an editing UI. Even if you don't fancy a Java solution, then why not a simple Access application. An app to provide friendly editing of a single DB table shouldn't take more than half an hour to set up.

Seems best option to me, keeping data in database and providing GUI to update it...

Thanks Frank everyone.