Starting my Next Database - Need Opinions?

[Jakboi]

Hello,

Well my first one is almost done and looking to start the next. This one like my last was in Excel but I like the flexiblitly of Access, so heres the next spreadsheet I was hoping to bring over.

Heres a picture as well to help visualize the excel spreadsheet:





Now for how it works and your if any opinions on how I should set it up. Each month I get reports on who the "Salespeople" contact and that has the following:

Client name
Date of Contact
Account number
Type of contact (phone, meeting, etc)
Intial Contact Date
Date Entered by "the salespeople"

I take the Client name and have to search the workbook. Clients are only counted for credit if they havent been contacted for the quarter yet. So only unique contacts per quarter.

I then have to mark who the client was contacted by, what salesperson or salespeople did the contacting. Then I need monthly, quarterly, yearly totals.

It would be great if I could search the database by account numbers and have it show me what quarter (s) they were contacted and by what salespeople...thats prob down the line though.

This is probably easy but I have done only one database so far and need a kickstart.

What tables would you make or any other pointers would be awesome.

Looking for any additional ideas or opinions on how I can transform this into a much easier access database.

 

[Jakboi]

Does anyone see this as being wrong or inefficient?

4 Tables

Table Clients
Account Name
Account Number

Table Contact
Type (meeting, conversation, notes, correspondence)

Table Dates
Date Entered
Initial Contact Date

Table Salesperson
Credit Given
Entered By

 

[Jakboi]

Ok. This seems to make more sense I think:

Table 1

Acount Name
Acount Number
Contact Type

Table 2
Contact Date
Date Entered
Entered By

Table 3
CreditGiven

 

[Jakboi]

Hmmm...this looks ok as well.

Table1

Account Number
First Name
Last Name
Business Name
Contact Date

Table2

Credit Given
Entered By
Date Entered

 

[wazz]

dont' forget to check/try the templates for ideas.

 

[KeithG]

How are you join your table together I don't see any Foreign Keys. Also I don't understand the SalesPerson table. Should this contain atributes such as Name, Location, Hire Date, etc.

 

[Jakboi]

dont' forget to check/try the templates for ideas.

In Excel?

Well there not templates...I just made them. If thats what you mean, unless I can make them to templates...not familiar with templates.

How are you join your table together I don't see any Foreign Keys. Also I don't understand the SalesPerson table. Should this contain atributes such as Name, Location, Hire Date, etc.

Foreign Keys..hmmm. See Im not that good at this and not sure if I completely set my first database right...so figured I would ask this time around.

Salesperson table...

Well I think I can go without. I needed a place to record who gets credit for the client contact. The same names will be used for the Entered By. Meaning if John and Jill meet client Joe. Then they would both get credit and one of them enter that into a system that gives me a report. However not all of them on that report receive credit...hence i have to go through it and find the ones that qualify for credit. So if my report has errors...I wanted to know who entered the data in their report as to find the source.

Dont know if that makes sense.

HOLD THIS THOUGHT

Just thought of something. The report I get is from a "store bought" fancy database. The report I get is done by running queries on that database. I think I am reinventing the wheel here. I talked to our ops person who is goin to set me up with the privileges to run the queries. I think i can jsut inport the whole document and start from there....I will be back when i find more answers.

See told you i was new...I dont work in IT I do this for a division of my employer...will be back.

 

[KeithG]

What type of db is the report from? You could use an ODBC driver to conect you Access db to the db the report is from.

 

[Moniker]

Jakboi,

The foreign keys thing is all normalization. I know that you've probably read that word a lot at least, but if you're at the beginning of a new DB, you have to have the basics down upfront. Otherwise, in a week or a month or longer, it's going to hit you and you will end up significantly redesigning your DB or completely starting over, salvaging what you can. Neither option is good for you or your employer, so here's a brief overview specific to your situation.

BASICS:
Simple normalization is storing data in one place and one place only. For example, if you have a client name, you do not store that client's name in more than one table. You refer to it when needed, usually through an ID field.

You do not store calculated fields unless absolutely necessary (almost never). Since calculated fields depend on numbers that may change over time, the second they change, your stored calculated field is wrong.

You store as little data as you can while being as robust as possible. Disk access is always slower than RAM access, and the smaller the DB, the faster it runs and the more exandable it is.

(NOTE: There is a lot more to normalization than this -- you can investigate on your own if interested.)

YOUR TABLES:
Table: Clients
Fields: Account_ID (PK), Account_Name, Account_Number

Table: Contacts
Fields: Account_ID (FK), Salesperson_ID (FK), Contact_Type, Initial_Contact_Date

Table: Dates
Fields: Account_ID (FK), Salesperson_ID (FK), Contact_Date

Table: Salesperson
Fields: Salesperson_ID (PK), Account_ID (FK), Credit_Given, Entered_By

(PK = Primary Key, FK = Foreign Key)
(NOTE: You don't want spaces in your field names. They offer way too many potential syntactical issues as well as other anomalies.)

WHY IT'S NORMALIZED:
I don't think you've provided enough information on what the responsibility of each person/account here is, but what I'm trying to point out is that nothing is repeated unless it's a Foreign Key. Primary Keys exist in one table, while Foreign Keys exist where needed to link back to the primary table. Except for the "Dates" table you've proposed, I think that Clients, Contacts, and Salespersons are all valid tables to contain Primary Keys on their own, and then supporting tables (who did what order with what client, etc.) have Foreign Keys as necessary to gather information.

The important part to realize here is that you have to be able to tie each table to at least one other table through a key of some sort (PK or FK), at least with the proposed structure you've provided. You'll never see a professionally designed database where a table exists in some weird ether of no keys. Without keys, the tables are just asking for data integrity issues, data duplication, mismanagement, and so on.

Finally, note that the same field included in more than one table is only a Primary Key in one table where it's referenced. The value of the Primary Key's reference (I.e., "Account_Name" here) is not repeated more than once as it's referenced by the Primary Key Account_ID. (You can make compound keys -- more than one field referenced as the primary key -- but they too are primary in only one table.) Every other reference to it is a Foreign Key. Without that, you have normalization issues again.

For brand new beginner's DBs, normalization isn't that big a deal because chances are 99%+ that those DBs will never be used professionally (although you'd be surprised). However, the second a denormalized DB is used by multiple users and depended on, it's not long before you are spending more time manually correcting things and otherwise rewriting the DB/sending out your resume than you are feeling good about your accomplishments.

 

[Jakboi]

What type of db is the report from? You could use an ODBC driver to conect you Access db to the db the report is from.

Well this is the place where they must have purchased it. If you mean what the type the database is, its SQL...not even sure if thats what your asking.

Heres where they must have purchased it. We use Addvantage to track clients.

http://www3.sungard.com/Addvantage/

 

[Jakboi]

Jakboi,

The foreign keys thing is all normalization. I know that you've probably read that word a lot at least, but if you're at the beginning of a new DB, you have to have the basics down upfront. Otherwise, in a week or a month or longer, it's going to hit you and you will end up significantly redesigning your DB or completely starting over, salvaging what you can. Neither option is good for you or your employer, so here's a brief overview specific to your situation.

BASICS:
Simple normalization is storing data in one place and one place only. For example, if you have a client name, you do not store that client's name in more than one table. You refer to it when needed, usually through an ID field.

You do not store calculated fields unless absolutely necessary (almost never). Since calculated fields depend on numbers that may change over time, the second they change, your stored calculated field is wrong.

You store as little data as you can while being as robust as possible. Disk access is always slower than RAM access, and the smaller the DB, the faster it runs and the more exandable it is.

(NOTE: There is a lot more to normalization than this -- you can investigate on your own if interested.)

YOUR TABLES:
Table: Clients
Fields: Account_ID (PK), Account_Name, Account_Number

Table: Contacts
Fields: Account_ID (FK), Salesperson_ID (FK), Contact_Type, Initial_Contact_Date

Table: Dates
Fields: Account_ID (FK), Salesperson_ID (FK), Contact_Date

Table: Salesperson
Fields: Salesperson_ID (PK), Account_ID (FK), Credit_Given, Entered_By

(PK = Primary Key, FK = Foreign Key)
(NOTE: You don't want spaces in your field names. They offer way too many potential syntactical issues as well as other anomalies.)

WHY IT'S NORMALIZED:
I don't think you've provided enough information on what the responsibility of each person/account here is, but what I'm trying to point out is that nothing is repeated unless it's a Foreign Key. Primary Keys exist in one table, while Foreign Keys exist where needed to link back to the primary table. Except for the "Dates" table you've proposed, I think that Clients, Contacts, and Salespersons are all valid tables to contain Primary Keys on their own, and then supporting tables (who did what order with what client, etc.) have Foreign Keys as necessary to gather information.

The important part to realize here is that you have to be able to tie each table to at least one other table through a key of some sort (PK or FK), at least with the proposed structure you've provided. You'll never see a professionally designed database where a table exists in some weird ether of no keys. Without keys, the tables are just asking for data integrity issues, data duplication, mismanagement, and so on.

Finally, note that the same field included in more than one table is only a Primary Key in one table where it's referenced. The value of the Primary Key's reference (I.e., "Account_Name" here) is not repeated more than once as it's referenced by the Primary Key Account_ID. (You can make compound keys -- more than one field referenced as the primary key -- but they too are primary in only one table.) Every other reference to it is a Foreign Key. Without that, you have normalization issues again.

For brand new beginner's DBs, normalization isn't that big a deal because chances are 99%+ that those DBs will never be used professionally (although you'd be surprised). However, the second a denormalized DB is used by multiple users and depended on, it's not long before you are spending more time manually correcting things and otherwise rewriting the DB/sending out your resume than you are feeling good about your accomplishments.

Wow thanks for the information and amazing response!! Yea I am almost done with my first one...hope its normalized correctly. I have a feeling some things in my first database may not be totally correct so thats why wanted to start this next one trying not to repeat mistakes.

In my first database I had everything in one table at first. Then I used the Access table splitter I think and it broke it up after I figured out that I should have everything in one table. That I beleived did the FK's for me. While it seemed to be more correct in terms of structure, it confused due to my inexperience. So while it works and seems fine...it got kinda complex in terms of my understanding and ended up feelin kinda lost.

That was some great information...