Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Table Design

I am designing a family payments database. The purpose of the database is to maintain the data used to track the payments made by family members. I’m working on normalizing the tables. Please comment on the proposed design. Please also comment about the best practice for column names.

One family member, Joe, will mail checks (prepayments) to another family member, Mary. Joe will pay for 75% of the expenses. Mary will pay the remaining 25% of all expenses.

The following information must be tracked: (1) Record the date that Mary received Joe’s check. (2) Record the date that Joe’s check cleared Mary’s account.


COLUMN NAMES
Please recommend the best practice for column names. The following is the text from (ID: 40222804) in the following EE question.
http://www.experts-exchange.com/Database/MS_Access/Q_28484501.html
Your original tables contained prefixes on all column names. Although this is not wrong, I do not recommend it since in the long run they just get in the way. You will soon tire of typing the prefix, especially since yours are longish. It’s also that many more characters you have to type before intellisense can kick in. When looking at tables/queries in datasheet view, the meat of the name will rarely be visible and you’ll have to constantly widen the columns to see the entire name. I understand the purpose of the prefixes and in fact when I was working in COBOL, I was religious about them. But, COBOL didn’t have intellisense and I had to type everything anyway. When working with a language such as VBA, I would use suffixes instead. They accomplish the same thing and won’t get in your way.


PROPOSED TABLES
Banks
BankID
BankName


BankAccounts
BankAccountID
BankAcct
BankAcctDepositDate
BankID


Checks
CheckID
CheckClearedDate
CheckDateReceived
CheckNumber


Checks_Received
CheckID
FamilyMemberID


Checks_Deposited
CheckID
FamilyMemberID
BankAccountID


Checks_Written
CheckID
FamilyMemberID
PrepaymentID


Prepayments
PrepaymentID
PrepaymentName
PrepaymentPurpose


FamilyMembers
FamilyMemberID
FamilyMbrFirstName
0
Mark01
Asked:
Mark01
  • 6
  • 4
  • 2
  • +2
3 Solutions
 
YZlatCommented:
I would add BankAddress and BankPhone and maybe BankFax to the Banks table
Also Checks table should probably have routing number as well
0
 
Scott PletcherSenior DBACommented:
Yeah, the column prefixes are, to me, somewhat overdone and annoying.

Identifying / linking columns, such as BankID, should be left prefixed, but basic data columns should not.  I'll explicitly mention a couple such names, then assume you get my preference for the rest.


Banks:
Change "BankName" to "Name"


BankAccounts:
Get rid of BankAccountID
Change "BankAcct" to "AccountNumber"
Add account type (checking, savings, etc.)
Clustering key should be ( BankID, AccountNumber ) --and AccountType, if required
Add RoutingNumber
What is the purpose of "DepositDate"?  Is it "last deposit date" for that account?  
    That is denormalized, since that value can be derived from other tables, but it might still be legit.

   
Checks & Checks_Received & Checks_Deposited & Checks_Written:
Why are these separate tables?  
A single Checks table could have all of this data.  Tables with 1-1 relationships should always be very carefully reviewed.
Since I prefer natural, meaningful keys when available, I'd remove CheckID and use ( BankID, CheckNumber ).


FamilyMembers:
Last name should be stored as well, since those can change.
0
 
PatHartmanCommented:
BankAcctDepositDate is something that would appear in a "transaction" table.  BankAccounts is a "master" table.

Checks should include BankAccountID as a foreign key.

The check logic is fuzzy.  Are you only tracking checks written on accounts in BankAccounts?
Does each person have a separate account?  If so familyMemberID needs to be added to BankAccounts.
If multiple people have authority over an account, you need a table to define authorized users for each Account.

I would use one table to record transactions.  It should include
CheckID
BankAccountID
Deposit/Check/Void
Check/ATM/Cash
CheckNum
PayorID
PayeeID
DateWritten
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Mark01Author Commented:
Thank you, PatHartman, ScottPletcher and YZlat.
0
 
Scott PletcherSenior DBACommented:
Btw, I would never store bank phone #s, etc..  Instead, I might store the home web page for the bank.  That is vastly less likely to change and you can find phone/fax/etc. numbers from there.
0
 
PatHartmanCommented:
@Scott -
Change "BankName" to "Name"

Using property names as column names results in potential issues once you use VBA.  Everything has a Name property so if you inadvertently type Me.Name, you'll be referring to the form's name property NOT your [Name] field.  You will always have to remember to encase this word in square brackets to avoid problems.   It is also best to avoid reserved words (especially properties and functions) and special characters including embedded spaces.  So, in this case "Bank" isn't a prefix as the OP had been using them but a qualifier to describe whose name.
0
 
Scott PletcherSenior DBACommented:
The column name should reflect the data it contains, period.  

I'm not reviewing a list of "reserved words" before assigning the name.  And I'm not naming columns differently just because programmers are potentially lazy/incompetent.

What names might be reserved in Java?  In C-sharp?  Why on earth would anyone want to restrict data names just because a particular development tool might be used and because the developer(s) might be too incompetent to use/delimit a column name correctly??
0
 
PatHartmanCommented:
If you happened to read the link to my earlier answer, I suggested compound names since they are unlikely to conflict with reserved words.

You have the same problem with certain words in SQL Server.  Case and Order come to mind as words I have seen as column names.  Both REQUIRE delimiters since they are reserved words.  The difference is SQL Server will simply not let you use the offending names without delimiters in a query because they create syntax conflicts.  VBA and other languages don't recognize a conflict.  They just do the wrong thing (or maybe the right thing but it is accidental).  So, rather than being arrogant about it, I take defensive programming measures to avoid the conflicts.  A programmer isn't lazy or incompetent because he forgets to delimit a problem name.  He shouldn't be put in the position to either have to delimit EVERYTHING or to recognize the names that require delimiters.
0
 
Scott PletcherSenior DBACommented:
>> A programmer isn't lazy or incompetent because he forgets to delimit a problem name.  He shouldn't be put in the position to either have to delimit EVERYTHING or to recognize the names that require delimiters. <<


Isn't that his job, to know his code is functioning correctly?  There are many potential back-end development languages, some of which might not even be known when column names are determined.  Moreover, "reserved" words are added over time.  Thus, there's just no way to be sure all reserved words will be avoided.  If the developer can't recognize when they are, then, yes, use delimiters EVERY time.

Btw, if the developer himself can't recognize the name is reserved in VBA, how on earth is the data architect or DBA choosing the column names -- who are NOT developers -- supposed to know that??  You seem to be claiming it's too much to expect the developer to know all the reserved words in their own development language, but non-developers choosing column names should absolutely know them all.  Doesn't that sound a bit odd?!

More importantly, choosing the best, most descriptive column name should not be artificially restricted because of a development tool ... sorry, but that is just a crutch for developers.
0
 
PatHartmanCommented:
You are assuming we are dealing with professional developers.  That is not the case with most posters in this or other Access forums although this forum probably does have more developers than most.  The people answering the questions are the professionals and we need to temper our suggestions to suit the target audience.  If I can prevent people from getting into trouble once they progress to the point of actually programming by guiding them in defensive techniques starting with naming conventions then that is what I will do. The simple technique of using compound names eliminates the majority of potential conflicts with reserved words in SQL, VBA, etc.  No one has to memorize a list or be bogged down with having to delimit everything.  And no one is going to get in trouble using "date" and getting the current date rather than the "date" from their record.

We can agree to disagree on this topic but we should probably conduct the conversation in private rather than continue to usurp this thread.  I like a spirited discussion.
0
 
Scott PletcherSenior DBACommented:
If they're casual developers, they should ALWAYS use delimiters around the names.

If they're unknowledgeable professional developers, they should ALWAYS use delimiters around the name.

I will never consider misnaming columns just to allegedly easy potential development mistakes.  It's a fool's chase -- reserved names are changing, and compound names could be reserved as well.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
>> More importantly, choosing the best, most descriptive column name should not be artificially restricted because of a development tool.

I'll agree with this and add that it should also not be artificially restricted by SQL Server.  Most of the reserved words could be used as columns names without conflict if the parser didn't artificially declare their usage in column names as a syntax error.  The verb names (Update, Delete, etc.) are the easiest to determine intent.  Common words (Date, Name, Function, etc.) only slightly more difficult.

But SQL Server is what it is.  And its rules have to be obeyed.
0
 
Scott PletcherSenior DBACommented:
The parser must have those restrictions, or it simply can't accurately parse.

If you have any doubts about the name, delimit it.  If you have any doubts about whether you have the right doubts, always delimit all names, in SQL and in development tools.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
>> The parser must have those restrictions, or it simply can't accurately parse.

Not true.  Context can determine how a word is used.  You could use the same name 1,000 different ways in an object oriented language simply by changing the context.  SQL isn't object oriented, but there's no reason that the parser couldn't apply context rules instead of arbitrarily throwing an error when a reserved word is encountered where a user defined word is expected.  It happens just fine in a lot of languages.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now