1 Reply Latest reply on Jan 31, 2011 8:38 AM by philmodjunk

    Understanding table development



      Understanding table development


      I am trying to understand the concept of one-one, one-many, many-many.  This seems to be critical as you develop the structure of your database.  I am not quite certain how far you take the concept….does each field need to be unique to that table?  Here is an example.

      I have three types of customers….individuals, schools & churches.  All have similarities, i.e. an address, city, state, zip, phone, etc.  I also therefore have the same three types of PROSPECTIVE customers – those that I am hoping will become customers.

      Would I therefore create six tables to capture their unique data (name, address, city, state, zip, etc which is unique to that individual, church or school)? 

      Or, would I create two tables for this data (one each for customers & prospects)?

      Or, lastly is it best to only have one table that had all of their unique data + one field with the variable data (customer or prospect)?



        • 1. Re: Understanding table development

          This isn't a law chiseled in stone, but most of the time, you'll have a much easier time of it if you use just one table. You can then use finds, relationships, portal filters and sorts to work with specific sub sets of your data (churches, schools, individuals). This really comes into play when you consider how your two differnt group categories interact. (Let see all church prospects...). You'd have a pair of category fields (one for churches, schools, individuals and one for Prospective or Actual customers.) Moving record from one category to another becomes as simple as updating a category field instead of copying the entire record from one table to another. With a single table, you can also easily create reports where you see your data grouped into sub categories and with sub totals computed for each.