Table Schema Revision

[jrob]

I'm a low-end intermediate user and am beginning to overhaul a database (currently 35 tables) I created a few years ago. It needs cleaning before I start adding many new sections and this time I'm trying to establish all my conventions / schema before proceeding.

I'd like to reduce the number of tables by creating a single line items table for each section. For example: the Contacts line items table would contain fields for: phone, email, fax, address, and notes. I'm thinking each would have its own fk for use with individual portals on the Contacts "Form" layout.

This is a huge undertaking for me and I haven't heard much about this concept (except from a user in this Forum) or why the use of separate phone, email, etc tables is the preferred standard.

If it matters, I'm also planning on switching from a Spider relationship graph to an A-B with the overhaul. Can anyone give input on whether the single line item data support table is a workable plan?

Thanks for your help.

[Jason Lane]

It sounds to me like you are talking about Database Normalization. If you're not already familiar with the term/concept, there's plenty of information available online. If you are familiar with it, but have a more specific concern, please elaborate.

BTW, I don't understand how creating separate tables for each 'section' is going to reduce your overall number of tables. I'm not saying it's the wrong thing to do, I just don't understand how that would lead to fewer tables. I might be missing something. Perhaps if you explained your existing structure, it might be more clear.

[jrob]

Jason - Thanks for the reply. I've never heard of Database Normalization, so I've spent a bit of time researching it and Cobb too. It's indicating separate tables are preferred for each group of information relying on a single primary key. I'm weak in relationships, but think consolidating ancillary tables doesn't violate this principle. However, my proposed methodology might lead to an over abundance of empty fields. Am I understanding things correctly?

Existing structure has sections (groups) like:

Contacts - standard info with minor variations

Accounts - for passwords, registration/sign-up info and similar

Documents - for media associated with Contacts' records

Events - has several sub sections like Calendar, Trips, Parties, etc

Each section/group has ancillary tables. Those for Contacts currently are: address, phone, fax, email, web, and notes.

Portals are used to view all Contacts info on one layout. I find a lot of time is spent with this structure handling relationships, imports, etc.

Now to elaborate on the current concern:

For the re-write of the file, while also adding more sections/groups,I thought I had three choices (if you know differently, I'm totally open):

1. Maintain the current individual table structure (wasn't my 1st choice)

2. Do away with tables such as address, phone, etc and create a set number of fields for each in the Contacts table (have tried this before and found it cumbersome and limiting)

3. Create one table to house all ancillary Contacts info (address, phone, fax, email, web, notes)

I wanted to go for door #3, but I can't find examples of others who use it - which could be a red flag. Also, my weakness in relationships might prevent me from seeing pitfalls.

On the positive side, I'm hoping to simplify and save time/tables by consolidating multiple ancillary tables into just one. Perhaps this could be done for Contacts, but not for sections/groups where ancillary information is more optional and arbitrary???

Friends have been urging me for years to get this file to a commercial level for their use - hence the concern about getting it done "right" in a runtime solution for them.

Bottom line: Better to stick with the tried and true structure for tables, or is new structure viable?

Again, thanks for the help. I'm hoping you can help walk me through this decision —JR

Edited January 30, 2010 by Guest

[David McQueen]

Hi jrob,

You may want to try to get a hold of a copy of Database Design for Mere Mortals

http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719

It is a generic look at relational database design and is both very good and very accessible.

What you are dealing with are the concepts of:

Entities

Attributes

Relationships

The entities are your tables and the attributes are the fields that carry the data in those tables.Relationships tie entities together. In terms of normalization, the basic idea is that any piece of data is only entered once into the system and placed into the proper entity.

You already have an entity - Contact. It has certain information common to all contacts and only needs that piece of information once.

You may want to consider an entity Communications. It would hold phone numbers, email addresses, instant messaging etc. It can hold many pieces of information per contact. Depending on the anticipated records per contact, you could have a secondary key in there so there is a contact key and then a function key say phone, fax, email etc so they can be displayed separately using a multi predicate relationship.

Your notes probably do not belong there. If you were to have a table of Actions, which would capture phone calls, letters, appointments etc. It would probably also capture notes. Again actions can be of different types, for example captured actions such as phone calls and letters...Things you want to capture as they happen ... and scheduled actions ... things that you actually plan out and set a time date and place to.

The other entity consideration you have to consider is the classic CRM problem. Is the database person centred or company centered. Do you need a companies entity and hence table and if so, is the focus of your program going to be companies, people or some combination of the two. This can be very important depending on the anticipated reporting that you will want.

An example of this is charity donations. Are donations given by the company, the person or both. Understanding that at the outset will lead to certain design parameters. I use this particular example because I have actually gone into potential clients with very large systems that would not function properly because the focus was on the wrong entity so donations were improperly atributed.Changing that focus after is very difficult because the relational structure is already set up to the wrong entities for their purposes.

HTH

dave

[jrob]

Hi Dave,

Much food for thought - thanks. I ordered the Database Design for Mere Mortals book you recommended which should arrive this week.

If I could start where you ended - CRM:

This is my personal database that others want for their personal use. It can handle business (mine) but I don't work for an outside company and so I've just added whatever I needed when the need arose.

In the Contact entity (thanks for the nomenclature, it makes it so much easier when speaking the same language) I have both name and company information. I took Apple's concept from the Address book (at least that's where I first saw it) and created a flag so I could designate whether the individual or company name was to be primarily used in this and other tables. I can still use the various name or company fields - such as in my check writer layout (from my Financial entity) to designate if a check should go to the person or company. I deal with many home businesses - so have to ask which to use as the payee.

Would I be correct in assuming I fall into the combination category?

...........................................

Your earlier paragraphs are harder, as they more directly address my confusion and structural flaws.

I was planning a "Communications" entity. However, I envisioned it just handling letters, faxes, and emails (single and mass.)

Also in my plans was a "History" or "Tracking" entity - to track all actions taken. Scary for me to tackle with my weak relationship skills - hopefully those skills will improve and open more options.

Your reference to "Actions" rang a distant bell. There was a solution I used which came with fp.6 (I think - it may have been earlier) that had this. I abandoned it with fp.7 and no longer build my files using another solution as a base. I thought "Actions" became less preferable with the new relationship graph that came with FM version 8 and moved away from it.

If I go back to that former structure, I'm not sure how to standardize overall. Do I create separate Actions tables for other entities? For example, I have a health entity that houses info for my doctors and can track vitals when necessary. It's related to the primary key in Contacts (so I can track health issues for others too.)

And yet -- I want to be able to go to a central "History" or "Tracking" layout and see what I did on a specific date - not just for Contacts, but for Events and Health and all entities containing user input. Would that be by relating all Actions tables to each other, or by having a centralized Action table?

In the finished version my friends also want to be able to have other family members, like a spouse or grandparent, log in and track their own information (again - I thought the relationship with Contacts would allow for this - plus properly setting up FileMaker's user accounts.)

This mish-mash highlights my weakness in table schema and is precisely why I need to throw out what I've done and do it over from scratch. The book you recommended will surely be of huge help in this area.

...........................................

Any and all input is welcome. I plan on devoting the next 3-4 weeks to reading, educating myself, establishing full written naming conventions and table schema, as well as standardizing a set of design templates. Also included in that time period is creating a formal file to house my development info (graphic elements, scripts, calculations, custom functions et al) as I obtain or create them.

In the following two months I will do what you probably get done in two days - create all tables, flesh out the dreaded relationship graph, and create all layouts.

Then comes the testing, importing data, script writing - which should take another two months for me.

In any event I hope to have something workable by mid August and in the process perhaps even make it to an Advanced level.

A final request: Because I'm switching to the A-B method I understand creating an ERD is not directly applicable. I've read Roger Jacques/Soliant's white papers on A-B but they're still a bit above my head. I'll muddle through, but if you know of some public sample solution which uses it, or of some simpler yet detailed information I'd greatly appreciate your passing it along.

Thanks for your time.

[David McQueen]

A few things:

1. Yes, you have a combination system - people and companies together. It will make your reporting easier.

2. A-B (Anchor Bhoy) relationship system

a. As you create your tables, line up the initial table occurrances down the left side ofthe screen. Give them each a separate colour. Any table occurrance you attach to these table occurances represents what you can see from any layout for that table occurance. Think of it as "I go to this layout which is in this table occrance and I can see through to these other table occurrances."

b. Do not attach these occrrances to each other. Keep them independent.

c. Then build all your relationships out from these occurances

d. You end up with a modular relational system. You can touch any module without messing up other parts of the relationship graph.

3. Not trying to sell anything here. Go to my site and the link to CRM software. There is a link to a QuickTime movie showing various programs in operation moving from simpler to more complicated. It is really a history of development. The entities are Contact, Action and WorkGroup in the main. It will give you something to look at and a different perspective.

These were all originally done in the FMP 5-6 file style. Becasue I was keeping it openfor older oeprating systems, I hadnot changed it. I am in the middle of a total revamp right now.

Dave

[bruceR]

"Each section/group has ancillary tables. Those for Contacts currently are: address, phone, fax, email, web, and notes."

Should be ONE table: Contact Methods:

ContactID

Type (email, phone, fax,etc)

Value ( the actual phone#, email, etc)

I suggest the Data Model Resource Book by Silverston.

[jrob]

Dave,

Thanks for the simplification of the A-B concept. Now I can "see" its strength. One question: a lot of examples I've found show a TOG header note, then the TO and then another note in the background with an additional TO on top showing an individual use/purpose. Hope I'm explaining this in an understandable way.

Is this something done regularly? Are there reasons when it might be advantageous as opposed to just creating another linear TO within the TOG?

.....................................

I went to your site and watched the video. Much of what's there is what I want to do, only I couldn't tell what the structure was. Once I know more, may I ask you about this again?

JR

[jrob]

Hi Bruce,

Thanks for the info. I'll be adding Silverston's Data Model Resource book to my growing library.

Re Contact Methods: This is perfect for my needs if I assume the following correctly:

1. Filter portals by type on layouts where they need to be separated for end-user ease

2. Separate filter for phone number formatting.

If above is correct - can end-user edit filtered data?

Thanks again for your info.

JR

[bruceR]

Hi Bruce,

Thanks for the info. I'll be adding Silverston's Data Model Resource book to my growing library.

Re Contact Methods: This is perfect for my needs if I assume the following correctly:

1. Filter portals by type on layouts where they need to be separated for end-user ease

2. Separate filter for phone number formatting.

If above is correct - can end-user edit filtered data?

Thanks again for your info.

JR

Not sure what you mean by filter. Auto-enter calc? Not sure what you mean by user edit. Note that now with script triggers we have that option as well. Why wouldn't you allow the user to edit the data and what does that have to do with data model?

[David McQueen]

Dave,

Thanks for the simplification of the A-B concept. Now I can "see" its strength. One question: a lot of examples I've found show a TOG header note, then the TO and then another note in the background with an additional TO on top showing an individual use/purpose. Hope I'm explaining this in an understandable way.

Is this something done regularly? Are there reasons when it might be advantageous as opposed to just creating another linear TO within the TOG?

.....................................

I went to your site and watched the video. Much of what's there is what I want to do, only I couldn't tell what the structure was. Once I know more, may I ask you about this again?

JR

Not sure what you mean on the TO's. In general, I try to use english names on TO's that describe the TO and where it is seen from and what is seen:

ContactFromEventSpecific

ProductsFromContactAll

ProductFromContactChosen

I colour code all of the To's. So if Contact, the initial TO is green, so is ContactFromEventAll

Anything you can do to give clear documentation will help you when you go back in a year later.

When you are ready, sure ask questions about structure.

It sound like you are having fun - of course I cannot see the hair being pulled out from here :-)

[jrob]

I'm afraid I expressed myself rather poorly. The goal is to provide a form type layout with all basic contact fields. The end user (myself or others) need:

1. to edit all such fields (phone, email, address, fax, web)

2. allow for unlimited entries in each.

3. automate phone formatting for domestic and international numbers

4. allow for buttons to send mail, open urls

5. allow for a check box to designate which is the primary entry of each

My question:

If all the data goes into a "Value" field in a related table with a corresponding "Type" field:

1. how do I create separate portal areas for individual items? (There's not enough room to group them all into one portal)

2. can these portals be sorted?

3. can this work if I need more than 1 value field per item (each currently has a value list of types and address has the standard multiple fields)

Please bear with my ignorance. Your advice seems more elegant than my skill set.

[jrob]

Of course I didn't bookmark the sites where I saw different TOs so I can't show you what I meant. My plan so far is:

1. Color the TOs automatically created by FileMaker with distinct colors and place them all vertically (fairly flush left) in the graph as you suggested.

2. Duplicate each and place those duplications on the top left side of the graph with a lower case "z" in front of each base TO name

3. Include a legend area along side the duplicated TOs to define any abbreviations or contain global note text

4. Duplicate the "z" TO when needing it for an additional TO and then change its name (so I can't mess up the color)

5. Place a horizontal note between each TOG which includes text designating its purpose

I'm still not clear on what naming convention for the TOs will be intuitive for me when working with scripts and layouts. I'm thinking lower case for the "from" table, upper case for the "to" table and lower camel after that. Perhaps upper for the primary anchor of the group, but I'm not sure that works for me. Yours seems so much easier, but since the graph allows for bidirectional I still get confused as to which is the "from." I need to settle that issue before I can drive on the one-way road.

You're right - I'm having fun and am overwhelmed with frustration. The concepts I get, the doing I don't. I know I don't have to say it again, but I want to thank you for hanging in there with me, it's truly appreciated.

[omegagoh]

Dear Jrob,

I think it is a situation when a developer trying to find a balance between simplicity and flexibility.

Usually, I lean towards flexibility, that is design to enable multiple line items. The fact that you are considering the concept of line item show the potential need for multiple line items else you may 'merge' the line item into the parent table (and possibly include a remarks field to allow additional info not belonging to any well defined fields).

As for A-B approach (scheme) for the RG, consider downloading a presentation from www.kevinfrank.com for better understanding. I think it is a good start as a low intermediate developer of FM. The integrated star approach use in many RDBMS is really powerfully but too complicated.

For naming standard, I would recommend that you adapt the naming standard from Core Solutions or myFMbutler.

Enjoy it!

[Vaughan]

I cannot see any advantage to the A-B method. Any "simplicity" is completely negated by the fact that the resulting A-B TO graph contains no useful information: it is impossible to work out how the database works.

Here is an excellent example: the FM Starting Point Template built by Richard Carlton Consulting. It is a extremely high quality piece of work. But take a look at the TO graph and explain to me how it works.

The weakness of A_B method is that the resulting graph is not self-explanatory.

[omegagoh]

Well, A-B is a method which enable the developer to focus on one perspective at a time. The sample given by you separate the system into many modules (TOGs) which were the simplicity. It enables you to look at a specific module at a time and its impact within its module should there be any changes.

Please refer to the attach image for a zoom-in of a section of the system I am developing. With the Legends on top, and taking the perspective from the Stock List, I would be able to find out if the item is sold (if it appears in the Invoice Line table). Further, from the Invoice Line table, which has the invoice number, I would be able to know the stock is sold to which customer specifically (via invoice number to Invoice table and customer id from Invoice table to Customer table).

In the zoom out view sample given by you, this advantage is not obvious.

Well, of course, it also has its disadvantages. The A-B method may over localized its scope of impact as the same base table could appears in other TOGs which the developer may not be able to assess its impact to the whole system easily. However, it is not easy to assess the impact using the integrated star method either.

As such, I think A-B method is still more suitable for new FM developer.

Regards,

Omega Goh

Speedy Scaleable Solutions