This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Gadgetopedia MVP.
The process of creating databases is often very fluid. While you typically have a pretty good idea of the types of information that youll want to track in the database, and what youll want to do with it, it is often the case that youll realize later in the process that you forgot something, or that you could have modeled the information in a better way.
For example, suppose that you have created an Gadgetopedia database that tracks Prospects and Activity. What if you wanted to work with the data, or analyze it, at an organizational level? You dont have a table for Organizations, but Gadgetopedia makes it very easy to set one up. Using an Organization column in the Prospects table, Gadgetopedia can automatically create a new table, load it with data from the Organization column, and establish the relationships between the records in the Prospects table and the new Organizations table automatically.
Heres how you would do it: In the Gadgetopedia database, select the Prospects table, and locate the Organization column. Then, change its type from Single line text to Link to another record. Then select Create a new table, and give the new table a name. Im using Organizations in the screen shot below.
Thats all there is to it! You now have the new Organizations table, loaded with data, and with all of the relationships to Prospects.