advice/opinions please!, - separate address table?

[awaddon]

Hi, I have used filemaker a few times but don't claim to be an expert, I have also used and developed web database applications.

I am currently creating a company workflow system in FileMaker 13 which will run off filemaker server.

A colleague of mine who happens to be a friend of the boss has taken it upon himself to design and build the initial database table structure, this admittedly has saved me a lot of time!, however I do not agree with certain decisions he has made, the main one being the table containing the customers, he has said all the customers, suppliers and staff should be on the same table as a 'contact table' which I see could be of benefit especially as some of our customers are also our suppliers. The staff I have moved onto a separate table just because I want to keep them separate for user logins and security reasons.

The main thing i disagree on is that he also is suggesting that all addresses for customers and suppliers are saved on the same 'contact table' as separate records but related the the customer and supplier records on that same table. I personally have argued that I would rather put all addresses on a separate address table and simply relate it to the 'contact table' as I see this as being a cleaner and more logical way to do it. There are hundreds of contacts each of which have at least one address, and lots have multiple addresses, surely my way would also be more efficient as when searching for a customer it would be searching through a smaller table, adding the addresses as separate records would double maybe even triple the size of the table.

I would value anyone's opinions as to how they think would be the better way to do it.

thanks

[OlgerDiekstra]

A flatter structure is easier to comprehend, and there may be situations where you might accept data duplication. I however, don't like it.

In your case, I would create a people table, and then create separate other tables such as customers, staff, supplier contacts, whatever, which relate to the people table and can have additional properties relevant to their use (you can have logins in your staff table for instance). Each person in the people table then has relations to other tables, such as contactnumbers and addresses.

The Address table has streetname and number fields, a relation to a city/places table, where cities/suburbs are defined. The city table relates then to states, and the states to countries.

I then prepopulate the cities/suburbs table (as well as the states/countries) with information freely available on the internets. I don't worry about streets. that will cost you money, and you probably end up having more information in your DB than you really need. Getting that city/suburb data imported also allows me to import long/latitudes and so I know where a city/suburb is as well and can do distance calcs.

[comment]

he also is suggesting that all addresses for customers and suppliers are saved on the same 'contact table' as separate records but related the the customer and supplier records on that same table.

 

I am not sure I fully understand this idea. What would happen with all the other, non-address fields in these records? Does he suggest they be all filled with the same data - IOW, be duplicates of each other? If so, then the answer is very simple: he is wrong and you are right. Just imagine what would happen if one of these fields had to be modified; you would have to replicate the same modification as many times as the number of addresses the contact has. This is precisely what a normalized relational database is designed to prevent: you want to store each fact in one place only.

 

 

I would create a people table, and then create separate other tables such as customers, staff, supplier contacts, whatever, which relate to the people table and can have additional properties relevant to their use (you can have logins in your staff table for instance).

 

I would not hurry to adopt this approach, as it is a lot of work and in most cases unnecessary. However, this has nothing to do with the question so I will not argue the point further.

[bcooney]

I am using a data model in my latest project that is party->party-contact-mechanism<-contact-mechanism. All parties, people and organizations are in the same table (even staff). Contact mechanisms consist of phones, addresses and electronic (email, Twitter, etc). With FM13, you can easily hide the fields you don't need, or use slide panels with on layout script triggers so that one layout serves all needs. Then, I have built popover pickers to easily find parties to add them to transactions.

[LaRetta]

With FM13, you can easily hide the fields you don't need, or use slide panels with on layout script triggers so that one layout serves all needs. 

 

Hi Barbara!

 

When you say that "one layout serves all needs", I would add a caveat ...

 

It is true that 13 hides objects much more efficiently but still, it is less costly to switch to a different layout for less-often used fields, portals etc than the burden of downloading the additional layout objects or evaluating the Hide calculations.  Slider, tab control and popover objects must still be downloaded even if the records on their hidden panels are not fetched.

 

Please see Page 8 and 9 of Design Performance by Mark Richman https://community.filemaker.com/docs/DOC-4168

 

And even Andrew Paulsen said in 2014 Product Conference about 13,

 

"Each object, even if invisible one, has a cost.  Tab controls – even if nobody looks at those tabs, FM still has to build up the layout objects for them and FMS still have to download all the model objects and styles in case they have to draw them."

 

These 'hidden' objects are wonderful in FM and I use them as well but different layouts are lower calorie.  If you want ultimate lean ... switch layouts. It is important to mention this so those new to FM don't begin putting everything on single layouts.  I've seen quite a bit of that and it can make a huge difference in the size of packet transfers and network traffic.  

 

The cost of an additional layout or two is minuscule when they are only evaluated when need arises versus a frequently-openend primary layout where the objects are downloaded, drawn and redrawn over and over multiple times a day as Users come and go.  I know you understand this so this is just to clarify for others.  :-)

[awaddon]

He is suggesting the record be a blank record in the contact table with only the address fields populated, the other fields would be left blank, so if I had 1000 contact and each had an address or two I could have 2000-4000 records on that table.

If doing a name search or post code search on a customer, would having all the records with those blank fields add time to the search?

[LaRetta]

The whole purpose of relational structures is to allow cardinality - one 'parent' (in this case Contact) can have many 'children' (in this case addresses).  There is no real problem with having empty fields but if you want flat-file structure, you could simply use an Excel spreadsheet.  

 

Searching your related Addresses table from Contacts is just as fast as searching the local table and, according to what you describe with having an additional 2000-4000 records in the Contacts table holding address, WOULD increase search time needlessly.

IOW, if a Contact can every have more than one Address, use an Addresses table and relate them.

One more thing ... it is always best to have narrow tables (fewer fields) even if that means 1:1 relationship.  

 

Contact mechanisms consist of phones, addresses and electronic (email, Twitter, etc). With FM13, you can easily hide the fields you don't need...

 

But this is not really correct because ALL FIELDS (except summary, unstored calculations and containers) MUST be fetched from server whenever you ask for a record - all of a record downloads and not just fields unhidden.  

It is in fact, hidden RECORDS which would not be fetched.

[comment]

He is suggesting the record be a blank record in the contact table with only the address fields populated,

 

Then the answer is a little less simple. He is still 100% wrong, it's just more difficult to show why. I would suggest that before you consider the merits in terms of performance, you ask yourself about functionality. Suppose you wanted to find all contacts in city X: you enter Find mode, type X into the City field, press return and ... you end up with 90 found records, 60 of which are entirely blank except the address - because those are addresses of contacts whose first address is elsewhere. You also may easily end up with duplicates, i.e. contacts that happen to have more than one address in city X. Those are just the first two things that come to my mind.  Now, did your colleague point out any advantages his method might have? Because I can't think of any.