Should custom data elements be stored as XML or database entries?

Question

There are a ton of questions like this, but they are mostly very generalized, so I'd like to get some views on my specific usage.

General:

I'm building a new project on my own in Django. It's focus will be on small businesses. I'd like to make it somewhat customizble for my clients so they can add to their customer/invoice/employee/whatever items. My models would reflect boilerplate items that all ModelX might have. For example:

• first name
• last name
• email
• address
• ...

Then my user's would be able to add fields for whatever data they might like. I'm still in design phase and am building this myself, so I've got some options.

Working on...

Right now the 'extra items' models have a FK to the generic model (Customer and CustomerDataPoints for example). All values in the extra data points are stored as char and will be coerced/parced into their actual format at view building. In this build the user could theoretically add whatever values they want, group them in sets and generally access them at will from the views relavent to that model.

Pros: Low storage overhead, very extensible, searchable
Cons: More sql joins

My other option is to use some type of markup, or key-value pairing stored directly onto the boilerplate models. This coul essentially just be any low-overhead method weather XML or literal strings. The view and form generated from the stored data would be taking control of validation and reoganizing on updates. Then it would just dump the data back in as a char/blob/whatever.

Something like:

<datapoint type='char' value='something' required='true' />
<datapoint type='date' value='01/01/2001' required='false' />
...

Pros: No joins needed, Updates for validation and views are decoupled from data
Cons: Much higher storage overhead, limited capacity to search on extra content

---

So my question is:

If you didn't live in the contraints impose by your company what method would you use? Why? What benefits or pitfalls do you see down the road for me as a small business trying to help other small businesses?

Just to clarify, I am not asking about custom UI elements, those I can handle with forms and template snippets. I'm asking primarily about data storage and retreival of non standardized data relative to a boilerplate model.

Answer 1

First of all, your application should never care about your data storage. Data storage, e.g., a database, is a detail. What your application should care about is its entities. Where these entities get there data from is irrelevant to the application. All your application should do is use an entity gateway to manipulate data, i.e., an interface which has methods for the entities. For example, an OrderGateway has methods like PutOrder, GetOrderById. The interfaces are implemented in the data access layer. Following this architecture, you can then implement whatever storage mechanisms you would like—database, file system, web service, etc. This approach leaves your application open for expansion.

Start with the most simple, which is usually basic file system access. Then, if your customers or users require it, implement other means of data storage. Don’t build things on vague assumptions like, “perhaps my customers would like to work on an SQL Server, or perhaps my users want to search on extra content”—you aren’t going to need it (yet).

Go with your XML-approach (or some similar file system-based solution). Worrying about storage overhead? Well, has it proved itself to be a problem? If not, then it isn’t an issue. Worrying about search capacity? Same thing. If it becomes a problem, then optimize accordingly. Early optimization and anticipated problems must be supported by strong evidence before they can be justified in software development.

Answer 2

I think your first model would be perfect if used with a No SQL database. I'm not an expert on No SQL DBs but as I understand it I think this would be a scenario where you might be able to eliminate the extra joins. Here's the page on No SQL DBs from Wikipedia. I'd take a read and see if that's a viable option for you.

Answer 3

I think your custom data elements would fall under #3 of this answer to a similar question:

Values are likely to change frequently and/or meant to be edited by non-developers, but yet this changes will not affect the logic - database with ORM or at least key-value storage with some user-friendly interface.

You probably don't want to be responsible for adding the custom data points for each of your users, so making it easy for them to do themselves should be a pretty high priority. Asking a small-business owner to add custom data points to (essentially) a configuration file is going to turn a lot of people off, and if you're already familiar with working with databases in code, storing them there will make it much easier to build a UI for them.

As long as your tables are indexed well - the Customer foreign key on CustomerDataPoints is indexed - the extra joins won't add an appreciable amount of performance overhead.