I would like to get some feedback on a design decision I need to make. I’m writing an online document management solution. We currently have an application written in PHP, I’m rewriting it using python/django. I have the following models: • Folder • Field • Document
A folder will have one or more search/index fields. The user can set up the following field types: • Auto-increment • Date • Email • Money • Notes • Number (user can specify min, max amount and whether there are decimal values (float vs int) • Phone • Text • User defined list • Yes/No – Boolean This part is all done. Now I come to the point where it is time to add documents to the folder. Let’s say I have one of each field type associated with that folder. So when I add a document, I can add values to the following fields: • Check Number (auto-increment) • Check Date (date) • Customer Email (email) • Check Amount (amount) • Memo (notes) • Invoice Number (number) • Customer Phone (phone) • Customer Name (text) • Category (user defined list) • Cleared (yes/no) My dilemma is how to store this information in the database. In our old system we have a document_values table with a record for each field in each document. Essentially, it contained the field_id (foreign key to field table) and the field_value which was just a text field type in MySQL. I’m not a database expert, but I don’t think this is very efficient. Here are some possible ideas I’ve thought of: 1. Create a table for each folder. I could base the database field type off of the user selected field type. The cons with this is that I would start to generate a LOT of tables as this system has the potential of creating thousands of accounts with multiple folders in each account. This would also take me out of the framework and I would be doing a lot of manual SQL. Another issue is when user make changes to their fields I would need to modify the field schema 2. Another idea is to have a model with the following fields: • Field (foreign key) • int_val (integer) • date_val (date) • char_val (char) • float_val (float) • text_val (text) • bool_val (boolean) Each of these fields would be null by default and I would only populate the data type I need that matches the field that the user set up. There were a few other ideas, but they vary slightly from the two ideas above. I think I prefer number two because it keeps me within the framework more, but then again, I could always do it the way our old system does it. It makes searching fairly easy when you only have to search one field. I would be interested to know of others’ thoughts on this. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
