Table occurrence naming

[LaRetta]

I name table occurrences in the plural and the foreign key in the singular, such as Contacts table and ContactID, Invoices table and InvoiceID.  I name the primary key always simply ID.

 

But what about a plural table occurrence name such as People?  What should be the foreign key?  PersonID?  PeopleID?  Or do I name the table Persons so I can then say PersonID and it sounds consistent?  Upon Googling, 'Persons' doesn't appear to be used much any more.

 

Sometimes it's the little things that can drive me nuts. 

 

Opinions appreciated.  

[comment]

Or do I name the table Persons so I can then say PersonID and it sounds consistent?

 

If each record in a table represents a fish, would you name it "Fishes" so that it sounds consistent?

[LaRetta]

Well I wouldn't now. 

 

But yes maybe I would be greatly tempted to rename it so it would be consistent ... wait I just thought of it ... Humans and HumanID.  

 

nomen est omen

 

By naming consistently, typing variables and field names in calculations and scripts is simpler. But no, I will probably not name the table Humans and you are correct in your example.  However, the urge to correct it is overwhelmingly strong so I will probably still search for appropriate alternate names.  

 

Homo sapiens?  Neh.

Citizens?  Earthlings?

[comment]

Language inconsistencies aside, there is something "wrong" about your People example. What do you mean, a table of People? Who are these people to you? What common function do they fill in your business to be included in a common entity? The only one allowed to have a completely generic "people" table is God.

[LaRetta]

What common function do they fill in your business to be included in a common entity? 

 

Good morning!

 

We have Staff who are also Drivers.  We have Drivers who are also Car Owners and sometimes they work in the Mechanic shop (Mechanics are not necessarily Staff).  We have NextOfKin to a Driver who also drives and four of our Suppliers also own eight cars.  So far, I have counted 82 cross-associations out of 1,100 people in 102 of these businesses.  That is not a high number but there are already major confusions in the prior solution because of these cross-associations, particularly revolving around money-movement but also scheduling.  My solution is to use a People table and then Staff table, a Suppliers table etc which holds the PersonID and then other details specific to the table.

 

With the central table, searching for a person will be easier and the deep complexities of paying/charging the various people (based upon contracts, salary, levies, mechanic fees etc will be easier to administer from a single Transactions table (financials are currently in seven different tables) while the People table will easily display the current financial responsibility of the person.  In addition, assigning a driver to the Roster means checking if they might have been scheduled as a Mechanic to work on a car that day.  I've used People tables before to great effect when cross-associations are an issue but that does not mean it was the best solution here, I realise, so I am very open to ideas.

 

BTW, another reason I am moving to this structure is to lean-up the relationship, decreasing the number of fields (in People) down to just critical for lists and searching (and serving up records) for use over WAN.  If you think I am making a mistake better to discuss it now than to have to unravel my knitting later.  I appreciate the input very much.  

[LaRetta]

I should also explain that most of these people are not employed but rather are considered consultants or associates.  Drivers may or may not be employees (depending upon the location and business); same with car owners and mechanics.  Add into that mix the customers who can come in for car servicing (not a company rig at all) or call to schedule a pick up as a regular fare but are on our books since they are regular customers who have accounts.

 

Even our owners use the limos to shuffle themselves around but they still have an account and must pay for the use.  It is very similar to another situation you helped on few months back where the OP was talking about rental properties (I think).  I totally related to the cross-pollination aspect.  ADDED: One car can have several owners as well.

[Fenton]

Much of this sounds like it could use the David Graham separation method. Which is, as you say, a central table for the fields they share, and other tables for the ones they do not share. The main thing he added was a method to have the central table automatically create (via relationship, not script) a new record, with the critical ID, and also set that ID into the "child" table, upon record creation in the child table's layout. I posted a file with an example somewhere here (long ago)

 

http://fmforums.com/forum/topic/65163-report-using-a-partial-join/#entry308948

[LaRetta]

Hi Fenton,

 

Yes, subtype/supertype.  By restricting People to only those fields required for finds and standard list views (9 fields now instead of the 316 prior), Users can search (and download those records from server) from a very fast table in list view or portal.  Once they select the person they want, they can view any portion of data (or role) that the person influences within the solution by GTRR (or setting global with that ID).  Download from server then would be restricted to only the related records to the single person.

 

From User perspective, when Dispatch gets a call "hi, this is Charlie Denton" they can type 'charlie den' and find out who that person is, what their various roles may be within the solution, how much they owe or we owe them and so forth.  Searching for People seems to be what every business does almost constantly and they will be viewing from iPad.

 

Hi Michael,

 

It sounds like I am trying to convince you; I suppose in a way I am but mostly I am explaining my reasoning for arriving at this perspective.  I am tipping this direction more and more since moving to mobile/WAN and losing the inherent speed of LAN.  We plan to take advantage of FMSDIFM as well.  But narrow tables (few fields) are much faster (since all fields except containers download from server) and 1:1 relationships add no complexity - just typing into field creates and maintains the relationship with Allow Creation ON).  BTW, I use modified entity-style and not anchor-buoy so impact is even lower and I can take advantage of the bi-directional flow of data.

 

Since we are going 1:1 (which I have always used but can now come out of the closet about), what difference does it make where we split up the fields as long as the primary fields they share (in this case name) are in the super type along with those needed for relationships?  Oh truly, if I am missing critical aspects then I want to know.  

 

I really appreciate the discussions, guys.  These are not easy decisions and this is the base of the solution I am currently building.

[comment]

So far, I have counted 82 cross-associations out of 1,100 people in 102 of these businesses.

 

Okay, that's reason enough for me. And I see how it can be really difficult to come up with a common description for all of those. I guess you have narrowed the choices down to "People" and "VariousAssortedIndividualsAndHangersOn".

I think "Contacts" can be a good choice, too - provided you're not using it already in the context of sales or as contacts (representatives) in associated companies.

 

 

EDIT:

I wrote this before your last post; so whether you were trying to convince me or not, you have.

Edited November 24, 2013 by comment

[LaRetta]

LOL - I just read your edit.  Whew! I can continue my design then, much more relieved.  And I am glad this came about - my asking about naming - because I ended up feeling better about my decision.  I also decided that, if later I realised I had made a mistake about it, I could fairly easily move the people fields over to Drivers, Owners etc. with little (but certainly some) impact.

 

And the table's new name is Contacts with ContactID.  Perfect.  

[Gilbert Osmond]

My two bits --

 

I have encountered this sort of organizational / data-schema question in my own work, and also have decided to use a centralized "People" table.

 

The reason for this is that a Person is (still) a guaranteed granular data unit, i.e. indivisible and also persistent over time. A Person's roles and affiliations can and often do change  -- a Client may become a Supplier, a Supplier could become a Service Provider, and Owner can become a Tenant, etc. etc. -- but the person's identity, characteristics, and often their contact info remain the same. I use various join tables to associate a given Person (through their Roles) with other elements such as Sites (real property,) invoices, etc.

[bruceR]

 

Much of this sounds like it could use the David Graham separation method. Which is, as you say, a central table for the fields they share, and other tables for the ones they do not share. The main thing he added was a method to have the central table automatically create (via relationship, not script) a new record, with the critical ID, and also set that ID into the "child" table, upon record creation in the child table's layout. I posted a file with an example somewhere here (long ago)

 

http://fmforums.com/forum/topic/65163-report-using-a-partial-join/#entry308948

 

Fenton, that looks like a great example file but I notice that several of the field definitions have errors.

 

For instance, in the CONTACTS table, field _Current_User_ID, calc definition = <Table Missing>::<Field Missing>

[LaRetta]

 "VariousAssortedIndividualsAndHangersOn".

 

Actually ... I think Individuals might be even better.  And it fits my insane desire that they match Individuals - IndividualID  - YAYA!

And I appreciate the file, Fenton!

Edited November 24, 2013 by LaRetta

[LaRetta]

And thank you also, Gilbert.   

 

Most solutions already have that aspect firmly entrenched and changing later just isn't feasible because it is entwined throughout.  But it is seriously worth evaluating for future solutions.

[Matthew F]

 

 

I name table occurrences in the plural and the foreign key in the singular

 

This is a funny thread.  I'm not consistent, but I usually prefer using singular Table names (Room, Country, Invoice, etc.)    People and PersonID might be an exception though.  If English treated humans like game animals (moose, elk, deer) then there would not be a problem. 

[comment]

If English treated humans like game animals

 

Where do I apply for a permit?

[Fitch]

Consistency vs. hobgoblins...