2 Replies Latest reply on Jun 4, 2011 1:57 PM by AliNayeri

    Database system with flexible fields

    AliNayeri

      Title

      Database system with flexible fields

      Post

      Hi all,

      I'm looking for a database application on Windows  (should work on XP and 7) that I could setup easily to track things such  as receipts, warranty information, contacts, etc..

      What I want is something that works similar to the contacts app on  the iPhone. If you've entered phone numbers and contact information on  the iPhone, you know that you can customize the fields and values for  each contact without really adhering to a preset list of fields. For  example, for one contact you can add a field called "Address" and enter  the contacts address while for another you can add a field called  "Address (Ireland)" and enter the contacts address in Ireland. You are  also not limited in the number of fields you can add for each  record/contact.

      If you've ever used traditional DB programs like Access, you know  that you have to create some tables with set number of fields, each of  which are designed for a particular data. In this system, you have to  think ahead and anticipate every piece of information that the record  should contain. I want to move away from this model towards something  that is more flexible.

      So, I'm wondering if FileMaker can be used to accomplish this or if anyone knows any programs or platforms that  would provide this. I was thinking of creating something myself with  Flex or Adobe Air (which has SQLite embedded) but I would rather not  reinvent the wheel if possible. It would be a bonus if I could sync it  with my laptop/phone and have some of the databases available on the go.

      Hope this makes sense. If you need more information, let me know and I'll try and clarify.

        • 1. Re: Database system with flexible fields

          Start with this idea, a related table with a description field and a data field. (Note: you will find this easy to use but difficult to work with later on.)

          Your parent record is probably an individual's name. The related fields are those you described above. This is essentialy what the application is doing and note that it doesn't have to worry about all of the other things that FIlemaker can do which is why an address book can do it that way.

          This isn't the final idea since one big venture in the biz world has been to use <Tags> on data which produces the result you mention but since you are a new comer this should be noted but wait awhile. You would need to learn how to use <Tags>.

          What a program does that uses this idea is to let the data be entered in the random fashion you explain and then when it comes to displaying it on a layout it storts through all of the data to find the properly tagged information to display in a certain area of the screen, etc.

          You might investigate Filemaker's export of XML, HTML, etc. or ask further using my post as a starting point.

          • 2. Re: Database system with flexible fields
            AliNayeri

            Dear Jack,

            Thank you for your post. I had a similar idea and actually made a prototype using Access (I'm not really a fan but this is all I have right now) to see how it would work. What I basically found is that I need at least three tables:

            1. a table for record data (tblData) which houses all the individual pieces of data (e.g. Jane's phone number in Ireland) complete with a parent record ID, a record type (string, numeric, date, etc.) and a record lable (e.g. "Phone Ireland")
            2. a table for parent records (tblRecords) which houses the parent record to which various pieces of data are linked to (e.g. Jane) complete with a unique ID, a name/label and if desired a folder ID to organize records in folders
            3. a table for folders (tblFolders) to organize the records

            Now this idea seems to work. It means that tblData will grow to be very big and that I need to make sure there is a way to export data at some point if necessary (and I think as you suggested XML is probably the way).

            The only problem has been trying to create a front-end where field customization can happen. I haven't used FileMaker but I'm assuming it is very similar to Access and that its forms would not allow for this kind of thing without some creative coding, if that. I know that you could just work with tables directly, but that would defeat the whole purpose of organizing your data. I would like to be able to have a similar system to what is used in iOS where you can edit your record and add custom fields. I would at the same time like to have the ability to create data templates, which would present the user with some pre-configured fields which they could choose to use or edit. I think the Contacts app uses this idea because for every new contact it gives you some default fields which you can choose to populate.

            The problem is that I'm not really a programmer and would have loved to be able to just get a piece of software that does this out of the box. I think there is a real market for something like this, especially for home and small business users. As I said I just want to be able to manage various pieces of information and the full database solutions are just too cumbersome to make it worthwhile. As an example consider trying to keep track of warranty information of various home appliances. Although there are standard sets of fields for all the appliances, there may be some information that is specific to a particular device. As it is now, I either have to think about this beforehand and accommodate it by including a field or use a general purpose memo field to store this information. The other thing I like about this approach is how clean the data looks when you are just browsing it. If the field is empty it is not shown.

            Sorry about the long reply. I'll try and see if I can accomplish this in a simple way. I've been thinking that if this is to be done with code, then an Adobe Flex/Air application with SQLite might be the way. However, I really don't want to spend a year trying to figure this out if it has already been done. If anyone has any insights I would be thankful if you would share.

            Regards,

            Al