Database Setup 3

[ssVBAdev]

Good Morning.

My background is mainly in VB and in the programming side of things. When it comes to Database Setup, I'm not sure where to begin.

I'd like to setup a "Client" database with a list of all my companies clients. I envision 2 tables: one with the client company general info and another with client names that would contain the name of the company they work for (thereby providing a link or connection to the other table).

This database would be used in Access (say by admin personel who control or maintain the database) and it would also be used by VBA functions in other programs such as Word and Excel to gather data from the tables within database for use in documents such as form letters, billings, quotes, and some even more complex uses (such as integration with AutoCAD - but that's down the raod a ways).

Is this making any sense? Is there easier ways to do this sort of thing right now. I must reiterate my lack of knowledge of databases. Is there information or samples I can see on the

http://WWW any

where? I have looked and not found what I am looking for yet.

Any information or assistance that you can give to get me started - or at least pointed in the right direction - would be appreciated.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[KenReay]

Hi

Your basic idea appears sound, personnally I would use an autonumber as prime key in Company table and use that as a foreign key in the clients table.

It is possible to write VBA for work excel etc to access the Access database no problem

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JeremyNYC]

I put a copy of Paul Litwin's "Fundamentals of Relational Database Design" on my website. It may help you feel more comfortable with designing a database. But if your data are really as simple as what you describe, Ken's bit of advice may be the only thing you need beyond what you've already got.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

ssVBAdev

Aside from company and clients, what specific puspose will this database server? I realize, you you are going to use VBA magic to pull off information.

Is this to be just a contact database - names, phone numbers, addresses, etc? Will the A/R, invoices, billing information be stored on the database? Will you be storing technical specification, licenses, connect strings? Will it be used as a knowledgebase?

Each one of these function will add to the complexity of the database.

Jeremy is one of the brightest stars in the Access forums, and the his link will take you to a document that will provide a good start.

My take is...
- Decide on functions and outcomes
- From here, work backwards to decide primary tables
- Use the normalization process to decide on relationships.
- Before spending a lot of time on forms and reports, enter test data, and make sure you will get the expected results using SQL. If not, review the design.

Three stumbling blocks frequently encountered are...
- Deciding on the primary key (not too hard, but people do stumble)
- Normalization, where information should be split on to another table, but the developer sticks it on to a primary table. Example: Phone number. Whoops, need home and office; well I will add a field. Whoops, now I need a FAX numbe; well I will add a field. Whoops, now I need a cell number, now two cell numbers... Whoops, the business just changed their phone number, and now I have to change the phone numbers for over 50 people.
- Problems understanding when there is a one-to-one relationship, and when there is a many-to-many relationship.

These stumlbing blocks can develop into headaches later with maintenance, data integrity and lack of functionality.

Richard

 

[ssVBAdev]

Thank you all for your responses. All of them have provided me with some info.

Ken: I decided not to go with an auto number as the primary key in the "company" table. My thinking was that I could use the Company Name as the Primary key. Each company should have a different name. But, where I run into a situation where there is the same company name (say, for example, in the case of a "branch" or "satellite" location) I will simply add a "keyword" in brackets after the company name; generally indicating the location of the company such as a city name. (ex. Your Company (Dallas), Your Company (New York)).

Make Sense?

Jeremy: Thank you for that link. I will be sure to check it out.

Richard: Well, there is much that I plan to do with the database but it mostly boils down to a large address book. We are a contracting firm and I plan to use the database information right from the start. That is to say that the sales department would use the information to correspond with clients even before a contract is awarded. Then, when the contract is awarded, the contract is given a number. A second database would contain information on the contracts. However, this initial database, again, is really just a list of contacts. It would be used for any document (theoretically) that can be produced by our office; Letters, Faxes, Transmittals, Purchase Orders, Quotes, Billings, etc. etc. etc....
I have actually split this "Main" database into a number of tables. One for the Company Information (Location, Phone # Fax # etc.), One for the Contact Person (or People) (Title, Direct Phone #, Direct Fax #, email etc...) and a few other tables just to make the data entry easier. The Company Table and Contact table are setup in a one-to-many relationship (although, admittedly I do not know much about relationships [again, I'm new at database creation]).
From this I've "cooked" up a letter template in Word that uaes VBA to retreive the information from the database compose the framework of a letter. Then the user simply adds to the Word Document, whatever it is they would like to add.

Whew... Sorry to be so long winded!!

I appreciate all of your initial replies and fully understand if a second reply (to my rather lenghty post here) may be asking for too much. However, I thank you all, in advance, for your time and look forward to receiving any reply's, and assistance, that you may be able to offer.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[KenReay]

Hi

While your method will work, have you thought about the implication if you are using Company anme as the key, and the comapny name changes (not common I know, but it does happen). Using your method the Company name may be stored as a foreign key in many tables, and you will have to set cascade update in your relationships (which may have a performance hit), to cause the update to propagate, but if you use 'my' autonumber method, then the autonumber value is stored as the foriegn key, and changes to company name do not need to be propagated,

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ssVBAdev]

Okay, I guess this requires some understanding of a "foreign" key. Lets say I have the Company Name as the primary key as it is now. And, in my Contact table, I have 15 people who work for that one company. If I change the company name in the Company table, will that not simply change the value to all of the contacts in the Contact Table? Could you please explain what a foreign key is?

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[KenReay]

Hi

The occurrence of the CompanyName in teh contacts table IS a foreign key.

The situation you are describing of have to update (say) 15 records, is exactly what I am talking about with 'Cascade Update'. Yes it can be done, but it is not ideal, and in a large datbase could cause performance problems.

I do not want to get too hung up on this, if you have only a few hundred records, then no problem you will not notice the difference, but who knows what may happen in the future teh database size may grow, so it is usually (in my exerience) best to do it right first time - a good friend of mine has an infuriating saying "it is never easier to do it wrong!", how true, how true

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ssVBAdev]

I agree in that I do want to do this "right" the first time and to prepare for future considerations. I guess I'm just having trouble determining what is "right." But, You have been of great help Ken. Have a star for your initial response, and for your time! But, if you have any more advice... please keep it comming. I'm all ears!

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[willir]

ssVBAdev

I have to agree with Ken's comment regarding the use of the company name as the primary key. He has given you information required to preserve data integrity - cascading updates.

I am going to make a suggestion on a variation on using the company name. A common approach is to use a code, in this case, a vender code. The vender code can be used as the primary key.

Why?
- You want to avoid things like "IBM" vs. "I.B.M.", "Dell" vs "Dell Computers". A real good example would be "Compaq Computers" which is now parrt of "HP". By using the full company name, I can see you ending up with duplicate venders.
- Less typing. For example, "New York Stock Exchange" vs "New York Stock Yards". If using a combo box to join to the vender table, you would have to type 16 characters before separating the two.

You can also use the code to differentiate between the same company and different locations.

The code would be a fixed length, say 6, 8 or 12 characters. It can be intuitive, ie "DELL" and "NYSTOCKE", or smart where you use a formula to derive the code. For example, "S" for supplier + "U" for USA + State Code + Name to give you SUNYSTOCK for the supplier New York Stock Yards in NY USA. I personally stay away from smart codes, but some people like them.

It is your database, but I just see problems with using the company name, the same name that will appear on the address lables, as the key.

Regardless of your decision, good luck.
Richard

 

[ssVBAdev]

Richard:
Thank you for your last post. Excellent points! A star for you as well for your time and suggestions.

You also said in your first post "Problems understanding when there is a one-to-one relationship, and when there is a many-to-many relationship."

Now I "think" that I understand this 'normalization' but I'm not too clear...

One to one simply means that there is one record in one table that aligns with one record in another table. Many-to-many means a similar except, as the name suggests, envolves many records in each table. There is also a one-to-many relationship correct? One record relates to many in the other table. Like, for example, a company telephone number. The company has one telephone number but it applies to many people.

I guess the point is to keep most of the "common" items in one table and relate them to the other records in the other tables.

Am I correct in my thinking? Any comments?


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[ssVBAdev]

Ok! Now I'm confused.

What's this talk about "normalization" and "Primary" vs "Foreign" Keys about?

I tried some tests on the tables I had setup using the Company name as the primary key. And, as I thought what was the warning being expressed, if I changed the company name, the Contacts table did NOT change therefore requiring me to change all the Contact records manually. Fine. Point taken.

Then I added a primary key to the Company Table. But, when I then changed the comapny name, it ALSO did not change the Contact table. I would still be required to go through and change all those records manually.

I am using a combobox to select the company name in the Contact table. This associates a Contact with a company. I thought that the database could be setup in such a way that if I changed the company name, it would change the records of the contacts to show the change automatically. Is this not the case? What might I be doing wrong?

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SUnderwood]

Gentlemen, If I may,

Yes you are correct. Pictorially;

.-------. .-------------.
| | /| |
|Company|-----|Employee|
| | \| |
'-------' '-------------'

One to Many

.-------. .--------.
| | | |
| Job |-------| Quote |
| | | |
'-------' '--------'

One-to-one (where one quotation relates to one job record, and the job record is distinctly different from the quote)

.-------. .--------.
| |\ /| |
| Pupil |-------| Class |
| |/ \| |
'-------' '--------'

Many-to-many


When designing your database tables, unless you are a seasoned DB designer, using the following spreadsheet structure and APPLLY THE RULES OF NORMALISATION AT LEAST TO THIRD NORMAL FORM. Otherwise, you will VERY PROBABLY end up is a relational mess.

That last part cannot be stressed more firmly.


APPLLY THE RULES OF NORMALISATION AT LEAST TO THIRD NORMAL FORM

In doing so you will identify the Primary Keys, the Composit Primary keys and Foreign Keys

The Spreasheet

.------+------+------+------+-----------.
| 0-NF | 1-NF | 2-NF | 3-NF | Relations |
| | | | | |
|fields| | | | |
|... |... |... |... |... |

|... |... |... |... |... |
'------+------+------+------+-----------'

Sean

 

[SUnderwood]

Have you actually gone into the relationships editor and MADE the relationships between the tables and SET referential integrity and the cascade update options?

 

[ssVBAdev]

Sean,
I need to reiterate that I am a newbie to databases. That having been said...

What the heck are you talking about?

I understand you explaination of relationships. But then you talk about "spreadsheet structure" and normalization of the "third normal form."

I consider myself to be a bit Excel-Guruish but... Huh???

What do you mean?

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[ssVBAdev]

Sean,
Missed you last post trying to figure out your first one...
I have gon into the relationship editor and have setup a relationship between the Company Name in both tables. Maybe I should recheck it just to be sure it didn't go all wacky when I added the new row for the CompanyID.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SUnderwood]

Lets run through the process of normalisation.

ssVBAdev, could you please paste onto the forum each table and the content of the tables in the following format

Table 1
field 1
field 2
field n..

Table n
field n..

 

[ssVBAdev]

Here is what I have so far. Please, because I am new, go easy (I have not used any "proper" naming conventions as of yet) But also, If you have suggestions, please let me know. This is by no means cast in stone at this point...

Table "Companies"
CompanyID (This is new, something I was playing with)
CompanyType (This is new, something I was playing with)
CompanyName (This is currently the PK)
Address1
Address2
City
State
Zip
Country
Phone
Fax
ShipAddress1
ShipAddress2
ShipCity
ShipState
ShipZip
ShipCountry
ShipNotes

Table "Contacts" (Client Company Employees)
ContactID (PK - AutoNumber)
CompanyID (Company Name from "Companies" Table)
Greeting (Linked to "Greeting" Table)
FirstName
LastName
Title
OfficePhone
Extension
OfficeFax
CellPhone
Pager
Email
ContactTypeID (Linked to "Department" Table)
Notes

Table "Department" (Various "Department" categories)
DepartmentName (PK) (Sales, Management, Field...)

Table "Division" (My Company Divisions)
DivisionName (PK)

Table "Greeting"
Greeting (PK) (Mr., Mrs., etc...)

Table "Employees" (My Company Employees)
EmployeeID
DivisionID (Linked to "Division" Table)
Greeting (Linked to "Greeting" Table)
FirstName
LastName
Title
Credits
OfficePhone
Extension
OfficeFax
CellPhone
Pager
Email
DepartmentID (Linked to "Department" Table)
Notes

Table "Projects" (This table will eventually be quite large including data that would be specific to each job (ie start/end dates, cost, location, etc. etc. etc...)
JobNumber
JobName
Client (to be linked to "Companies" Table)
Street
City
State
Zip


Whew!
Well... you asked!


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SUnderwood]

Right, for now forget about the discussion about "what is an appropriate primary key". Of those data field you have give, we must chose an appropriate primary key

For COMPANY we'll use "Company Name"

For CONTACT we'll use his "Full Name"

For EMPLOYEE we'll use his "Full Name"

For PROJECT we'll use "Project Name"

For now we'll ignore the &quot;<Field Name>ID&quot; entries as they are meaningless data (their *function* can be brought in later).

Now, the rule of first nornal for is

&quot;Remove ALL repeating groups&quot;

...

And I have just realised that I must catch a train in 20 minutes! Right, I'm going to print out your tables and look over them. When I come back on-line in 2 hours I'll teach you have to fish ;-) It sounds as if giving you the fish will not be of help to you in future.

In the mean time it would be good if you can do a google search on NORMALISATION or NORMALIZATION and have a quick read on 1st, 2nd, 3rd normal forms (it simple, your a programmer, it'lll come naturally)

ok, back in a while.



International Applications Development
Business and Data Anyalist
Database & Database Applications
Web-Applications
VB, VBA, ASP, SQL, SSADM
email:sean.underwood1@hotmail.com

 

[willir]

ssVBAdev

Speaking to your latter issues, have you defined your relationships?

While in the database view, where you can see your objects - tables, forms, queries, etc, from the menu, select Tools -> Relationships. If this relationship form is blank, then you still may need to do this task. Right click and add your tables (all or some). If the tables are displayed and there are no joining lines between the tables, then the relationships have not been defined.

Before we start, a brief review so we are starting on the same place...
The primary key is a field used to uniquely identify a record. In the NY Stock Exchange, IBM identifies IBM of course. Each stock has an identifying symbol. Same for states in the US. So too should every record in a table. This is the primary key. You have selected not to use autonumber which is fine. Just makes it a little tougher in some ways if just starting out.

The foreign key is the primary key appearing in a linked table. A typical example is the the invoice header and inovice detail records - you have recieved a bill I am sure. Let's use your phone bill. The invoice header is the part with your name, address, due date, totoal amount owing, etc. The details are the transactions for each phone call and taxes. For this example, the invoice header used your phone number as the primary key. This appears once and is used uniquely identify you. Each transaction will also use your phone number to link to your account and the invoice header file. In this case, the many tranactions all use your phone number as a foreign key to link your account. In this case, the phone number for the transactions does NOT uniquely identify the phone calls.

The one-to-one (1:1) relationship is not used very often, but could for example, be used to split off confidential HR info from the rest of your public HR information.

One-to-many (1:M) is common, an dfairly simple. The aforementioned example of a phone is one example. Access handles this very nicely provided the design has been setup.

Many-to-many (M:M) is a complex. Since you are working, among other things, on a contact database, I will use addresses. A person can have more than address - business address, home address. But an address can have more than one occupant - at home - husband, wife, kids; at work - all employees work at this street address.

So back to the design...
Although I have described addresses being M:M, in the business world, a 1:M may be acceptable, and I will assume some basics for this example...

tblClient
CleintID
LName
FName
VenderCode
+ client info

tblCompany
VenderCode
CompanyName
+ company info

In this example, we assume that one comapny can have many clients so the VenderCode, the foreign key, is included in the client table. The foreign key appears on the &quot;many&quot; side. Click and select the VenderCode from the client table and drag it to the VenderCode on the Company table. A window should popup to edit the relationship. To properly enforce relationship, select &quot;Enforce Referential Integrity&quot;. Since you want to cascade updates, select this option too. NOTE: If you have data in your tables that breaks referential integrity, you will get an error message, and will not be able to select these settings.

On to M:M...
Using your client and company tables, lets say that we have a M:M relationship - one company can have many clients, one client can have many compnaies.

In this case the you can not put the primary key on either table since you will not be able to retrieve the info you want. You need to create an intemediary or join table.

tblClientCmpny
ClientID
VenderCode
+ any thing unique to the Client x Company table
The primary key is the ClientID + VenderCode. To set this as the primary key, in the table design mode, select the ClientID, hold down the CLT and select VenderCode, still with CTL down, right click and choose primary key.

Using the same principle described in designing the relationship, graphically define your relationships.

Hopefully this help explain things some what.
Consider reviewing Jermey's link -- it gives a more graphical description of what I have walked you through.

http://www.ABCDataworks.com

Also, the hands-on Access books are not bad for this type of thing.

Richard