Generally speaking, it’s really not that difficult to create a Flexile database. However, there’s some considerations that’ll make it easier for you to use Flexile. One of the most important things to remember about Flexile is that it keeps all of your Links right there along with each record. For all practical (and it is quite practical) purposes you can consider the Links of a record as part of the Record data. This can have a profound impact on the way you design your database.
Keep Your Tables Abstract When creating tables, you want to create tables for more generalized types of data rather than for specific pieces of data. At first, especially if you’re new to databases, it might be tempting to think that you can use tables for specific pieces of data. I’ve seen people start out by creating a table for a particular person, for example: John. This will quickly create a problem, though, once you start adding fields. What are John’s fields? You might create a Note field to keep track of notes for John, but once you add more fields, (say, Phone Number or Birthday) you realize that each record you add to the John Table creates either a lot of duplicate data or empty fields, since each field will be replicated in each record. So, for example, it makes more sense to create a table for all of your “Contacts” rather and add “John” as a record in your contacts. Then you can add fields that make sense (Name, Note, Birthday, etc) and the “John” record can be linked to other records easily.
Go Wide… Don’t Consolidate Your Tables You generally want to create more, smaller tables than fewer, larger ones. The reason for this has a lot to do with how Flexile works. Because it’s so easy to Link records to each other, it makes more sense to split your data into separate tables.
For example, it might make sense to include an “Address/Location” field in a “Contacts” table. However, if you create a separate “Addresses” table and associate the correct “Address” with a “Contact” you’ve accomplished the same thing and you can also associate other “Contacts” with that same “Address”. This will make it easy to find all “Contacts” at a specific address by opening the record for the correct “Address” and simply looking at that record’s Links. It also makes editing data easier. Let’s just say you accidentally got part of the address wrong and need to change it. If you have a separate “Address” field in the contacts, you’ll have to edit each Contact with that address. However, if you’ve only Linked the records to one record in the “Addresses” table, you’ve only gotta change that one record.
Another example of this would be “Phone Numbers”. Most people have more than one phone number (cell, work, home, etc) and some people just have an abundance of them. You could add, say, three separate phone number fields to your contacts table (Home Phone, Work Phone, Mobile Phone) but then not everybody has exactly 3 phone numbers. So some records will have empty fields (no Home Phone) while other records won’t have enough fields (wait…she has TWO mobile phones?). So create a new table called “Phone Numbers”. Add a Field for the number, and a Pick List for the Phone Type (Cell, Home, Work). Now you can add as many phone numbers to a record by Linking to them.
This brings us to the moral of the story. If you find yourself adding sequential fields (Phone 1, Phone 2, etc or Address 1, Address 2, etc) to a table then it probably means you need to create a new table for those fields. Because Links are displayed right along with the record, the Links become a kind of “field extender” for each record, allowing you to dynamically ‘allocate’ fields to the record by Linking to another record. You usually want to avoid an overabundance of fields in a table. That’s normally a sign that you may need to have more than one table. Not always, mind you, but often. When you’re creating a table, it helps if you try to think of how the table will interact with other tables through Links. By doing so, you’ll often see that one “large” table would be better as two (or more) tables that have Links between them.
K.I.S.S Your Database Try not to be overly complex. Try not to create too many “subtables” used as “linking tables”. If you have no idea what I’m talking about, don’t worry about it. This warning is more for people who are used to dealing with databases and have used subtables to link together two other tables (this is essential in a normal relational database to setup many-to-many relationship…which Flexile already provides). Subtables are often used in other databases the same way that Links are used in this one, except that they tend to be more complex to setup and manage. I’m not saying that you can’t use subtables (I use them sometimes) but often they’re unnecessary when Flexile provides an easy way to connect records to each other.
That being said, using subtables can provide additional information that a standard “Link” can’t…it just comes at the cost of complexity (and possible confusion). For example, if you’re wanting to keep track of your diet, you might create a Food Journal. In the Food Journal you’ll probably keep track of: Food Name, Calories, Protein, Sugar, Fat, etc. However, you’ll quickly notice that you enter the same information multiple times. So it makes sense to create another table called “Foods”. Now, instead of entering the same data in multiple times, you just Link to the “Foods” table and “pull” the appropriate information in to the Food Journal Table (using Aggregates). Now, as a topper to all that, let’s say you have another table called “Health Journal” you use to keep track of daily statistics (such as calories/sugar/fat/protein eaten, how you felt, whether you worked out, etc. You’re using Aggregates to pull information from the Food Journal (to get total calories, for example). Now the Food Journal as become a kind of “subtable” or “Linking Table” between the “Health Journal” table and “Food” table. This is ok, and probably the only way to accomplish what you want. But you can see how complex it can get.