> On Dec 25, 2015, at 12:51 PM, Stephen Chrzanowski <pontiac76 at gmail.com> > wrote: > > *Part 2;* > > More along with your application style, but a complete database schema > overhaul, think of a contact form that allows for multiple methods of > communication. Multiple email addresses, multiple phone or fax numbers, can > all be associated to one contact. Typically you'd have a table sitting aside > with the contact type (email, phone, fax, maybe in the future Telepresence > ID?), and another table containing the actual data. You could adopt this > method to what you're describing. To add or delete fields to your UI > (Telpresence info doesn't exist in any common contact manager I know of), all > you'd have to do is add or delete rows to a table, and your application > written to adapt to random(?) changes to the field changes, regardless of > additions or deletions. > > All you'd need is one table that would hold the fields unique identifier, a > field title, the order in which it is displayed on the UI, and possibly a > default value field. > > Another table contains a unique identifier, a FK field pointing to the UID of > the above table, and the raw data. > > Your software would then make whatever required SELECT call to obtain the > required information and either store that data in a new temp table, or, > store the data in memory either via a stringlist or class, then render the > data to your UI using just this new data.
This is called a dynamic database. One table holds the schema with one row for each field/column. Another table holds the data with one row for each field/column. Use joins to put the whole thing together. I used the scheme for an iOS app where the user can define how many fields there are for each "row" of the data they are saving. Like the Contacts app mentioned by Stephen. The trick is to maintain integrity. For example, when deleting a field be sure to delete all the data associated with that field. Use constraints on foreign keys to help during development (find those bugs early). Jeff
