Re: Is this a Sound Database Design? -- Table Structure comments

Hi Curtis,

Your customers tables should be several tables...

*Customers*
CustID, autonumber
Company, text
DBA_name, text
etc
--> JUST the fields that relate DIRECTLY to the company

since you may have several contacts per company, they need to be in a seperate table

*Contacts*
ContactID, autonumber
CustID, long integer
Lastname, text
Firstname, text
Title, text
BirthDate, date
etc

anytime you have a field ending in a number, like Principal1, Principal2, etc -- that should be your clue that you need to seperate that information out into another table

don't use spaces or special characters (like &) in names

Discover & Amex --> Discover_Amex

*Addresses*
AddrID, autonumber
Address, text
City, text
State, text
Zip, text

*CustomerAddresses*
CustAddrID, autonumber
CustID, long integer
AddrID, long integer
AddrTypeID, long integer

*ContactAddresses*
ContAddrID, autonumber
ContactID, long integer
AddrID, long integer
AddrTypeID, long integer

*AddressTypes*
AddrTypeID, autonumber
AddrType, text

personally, I put all companies and humans in a People table so there is just one place to relate addresses and phones, but I thought this might be a bit complicated for you, so I seperated them -- but this is really not the best way to do it

*Phones*
PhoneID, autonumber
Phone, text, 14, InputMask --> !(999) 000-0000;0;_

*CustomerPhones*
CustPhoID, autonumber
CustID, long integer
PhoneID, long integer
PhoTypeID, long integer

*ContactPhones*
CustPhoID, autonumber
ContactID, long integer
PhoneID, long integer
PhoTypeID, long integer

*PhoneTypes*
PhoTypeID, autonumber
PhoneType, text

If you do not want to make a Phones table (adds another level of complexity with the InputMask), then throw a Phone number field into CustomerPhones and ContactPhones -- it will be duplicated, but, with the InputMask, it can be difficult to lookup


You have a "Transaction Fee" in your Customers table... perhaps you need a Transactions table

What does "Account Paid" really describe? Do you not need a table with account information and another table with payments to accounts?

You have a table called "Payout Residuals" and the first field listed is MID ... what does MID stand for? Is it an autonumber field? If so, it should be the primary key for your table. In my opininion, it is best for all tables to have an autonumber ID that is also the primary key. In this table, you also have "Total Monthly Volume" -- this seems like a calculated field -- adding up monthly volumes from somewhere else.

Keep in mind how long your data is and name your fields accordingly (since you can't wrap fieldnames)

for instance:
Trouble Ticket ID --> TrouTickID

The Trouble Ticket Details table should have an autonumber field --> TTickDetID

I like to name the primary key ID according to the table name. You have a table called Followup Notes, but the primary key is named Call ID. Why not name your table Calls or name the primary key NoteID?

Keep names short (you will appreciate this when you write code). Users do not need to concern themselves with cryptic names -- use the field description to get more descriptive and do not use captions in the table design.

there is more -- this is just a start!

Think of it this way -- every "noun" should be in its own table. Then, the fields that describe that noun are like adjectives.

I will post code for subform sourceobject switching in a moment...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

Your awesome, it's great to have people out there willing to help! Access is so darn cool as it can do endless things & still learning over the years the power of this one program...

Here: http://www.gotmerchant.com/dbrelationships.jpg

I know, I need to get the spaces out of the names, but will fix that one day....

Any way possible to view the VB for the entire database in one screen, so you can change all the references to a particular name, use the replace tool & get it all fixed very easily?

Got an example of what you mentioned below? You have one subform and command buttons, which will change the subform, which form it displays? If so, got an example I can see the code & see how that it is done & use it?

I only have one main table and a few others that include different info tied to the main one, you see it in my pic above. The other tables are mere pull down info tables, etc....

Its hard to make it look pretty & still have all the cool features & simply all the stuff you want on one screen.....

Curtis

.

Loading