I’m slowly moving my databases onto PanX as I’ve moved from an iPad to a Macbook Pro.
With the new relational database manager, I have a question or two.
My old database had everything in it as fields, even multiples of similar data such as phone numbers, addresses, email addreses. So I’d have fields like “Email1”, “Email2”, “AddressLine1_1”, AddressLine1_2", etc.
Am I better separating these into different databases? For example, some people have more than one address or multiple email addresses. Is it best to just keep adding fields or to create a new databse everytime some data has multiple values for the same field?
In part it is a design choice question, as Jim said. The one design consideration is if you add enough extra fields there may come a performance hit. Which is why the Advanced Pane of Preference defaults to a limit of 150 data sheet columns. That can be adjusted and much larger values may work fine in some cases. But unlike LOTS of records, or LOTS of entries in one cell, having LOTS of individual fields may work but fairly slowly.
So planning to grow up to 100 email fields is probably a poor choice. But storing 100 different emails in one field of a record, 40 in the next record, etc. is fine. It’s just up to the db designer to provide some way of letting the user know they are there and and a way to access them.
Oftentimes, fields that were traditionally separate can be combined. For example, How often do we have City, State, and Zip as different fields, yet we never have to deal with them separately? Sure, if you need to select/group something on city or state or zip, that’s different - sort of. Because if you have zip code in the same field as city and state, you can select “contains” that. Sometimes you just need an address field that has the mailing address block (adr/city/state/zip) with no need to separate them.
For phone numbers you can combine them in one field with a description. Like
Work: 123-456-7890
Home: 124=567-8901
Cell: 456=789-1234
If you want all the people with phone numbers in the same area code you can get it with a select contains and you don’t have to visit three separate fields.
One of the problems of coming from an era where printers were chain-driven, the paper had green and white bars, and discs came in cake carriers, is having to forget - as in not use - what was done yesterday if today offers something better … says the guy who is still using lookup instead of left joins
Given your brief example, I would not suggest working that hard to assuage the multiple emails and addresses that a contact might have. It would be unlikely that anyone would have more than 3 emails that you needed to track. And addresses are typically limited to 2.
If you did want to post the other multiple fields and share this on the forum, we could better make recommendations. At the moment, it just looks like a typical contact db (says the guy with a people db that has 87 fields.)
I’ve faced this decision in my office management suite. It could be that combining multiple fields into one to keep the total number low is needed, but in other places - graphics and formulas - you will always have to parse out which element of the array or text block is the one you want. That could add a lot of complexity to the coding.
My solution has been to make the field structure simple and obvious. With the new native version of Pan X, the slowdown seems to be a theoretical consideration if using a Mac with M1 or M2 processor. I’ve had to raise the field limit to 400 on several of my key databases that have pushing 100k records. Using lookups to fill the calendar, for example, does over 1000 lookups into those databases, happens in a split second when I flip from one week to the next.
Long ago, when speed was more of a consideration, more knowledgeable contributors suggested putting the most used key fields at the front of the list so they are found more quickly. That can still make a difference if you have heavy searching/sorting requirements. My default sorting of the databases puts the newest records at the top (sort down) so they are found sooner - this also helps. But even displaying a customer account on the Ledger Card screen that has to find every pertinent record back to 1999 is quite quick.
