Database design help please

I need help designing a simple database. I thought it would be easier and I have become thoroughly burnt out It seems simple enough but I need help. I want to create a DB for customer records for my wife and her hair salon. Nothing fancy and just needs to replace the current index cards they are using. I have Access 2000. I can create a single table DB and it seems to work, but when I try to divide and create more tables, I get messed up. With the following fields below, what would be a good design? The relationships are also a bit confusing.

tblCustomers
------------
CustomerID-Autonumber PK
TelephoneNumber
LastName
FirstName
MiddleName
Street
City
State
Zipcode

tblServices
------------
CustomerID
DateOfService
ServiceDone
Results
PriceOfService
Operator

tblOperators
------------
Employee1
Employee2
Employee3
Employee4

The customers can have more than one service.
The customers can have more then 1 operator but only one per service.
Thanks in advance,
sorebrain

tblCustomers
------------
CustomerID-Autonumber PK
TelephoneNumber
LastName
FirstName
MiddleName
Street
City
State
Zipcode

tblServices
------------
ServicesID Auto-Pk
CustomerID PK
OperatorID PK
DateOfService
ServiceDone
Results
PriceOfService

tblOperators
------------
OperatorID Auto-Pk
ServicesID
EmpFName
EmpLName

That was quick

Thanks for the quick reply. I will go to the other pc and have a go at it with this design. I am sure to have a few more questions as I ran into some situations I didn't understand with creation of the form. More on that later,
I appreciate it,
sorebrain

In the design you provided, would the relationships go from #1 to #2, and from #3 to #4?
Thanks,
sorebrain

Crilen007 said:

tblCustomers
------------
CustomerID-Autonumber PK #1
TelephoneNumber
LastName
FirstName
MiddleName
Street
City
State
Zipcode

tblServices
------------
ServicesID Auto-Pk #2
CustomerID PK #3
OperatorID PK
DateOfService
ServiceDone
Results
PriceOfService

tblOperators
------------
OperatorID Auto-Pk #4
ServicesID
EmpFName
EmpLName

Click to expand...

CustomerID-Autonumber PK
TelephoneNumber
LastName
FirstName
MiddleName
Street
City
State
Zipcode

tblServices
------------
ServicesID Auto-Pk
CustomerID FK (Foreign key for Customer)
OperatorID FK (Foreign key for Operator)
DateOfService
ServiceDone
Results
PriceOfService

tblOperators
------------
OperatorID Auto-Pk
ServicesID
EmpFName
EmpLName

The service table pulls from the operator and customer table. The link is the FK.

Thanks, now onto the form

Ok, I think I got my relationships correct but here is my next problem. When I use the form designer and select all the fields from the tblCustomers, and the

DateOfService
ServiceDone
Results
PriceOfService

from the tblServices, I get the original problem thats been kicking my behind. I can step through the form and enter all the first tables fields, then I get to the DateOfService field (that has a 0 in it) and just hit Return, then when I attempt to enter text in the ServiceDone field I get the error:

Cannot add record(s); join key of 'tblServices' not in recordset

Now I know I created the form using the wizard and selected the tblServices and the way I wanted all the fields to show on the form so I only have one control for stepping through the records, but thats the way I want it. So you can add all this info at once. Not with having two separate controls. How do I fix this?
Thanks,
sorebrain

One other note; I know that after I exit the form when I get the error message, I can go back and then it will allow me to add the info in the tblServices table. I just need to understand why or how to get around this.

I think I figured it out...

Well, I guess I will be moving this discussion to the Forms area but I think I figured out my confusion. Initially I was adding the 2 tables to 1 form and trying to create the complete record, but I had 2 navigation controls. I think I need to use the sub-form, by creating 2 forms, each with 1 table and a linked field, and then add the 2nd form (the tblServices) to the main form (tblCustomers), thus giving me one navigation control. I may be wrong but I will sleep on it and try again and repost in the Forms section later....
Thanks,
sorebrain

I would make some modifications to the tables. There is no reason to have a separate table for operators. The customer table can be used. I would guess that some of the operators are customers also.

tblCustomers
------------
CustomerID-Autonumber PK
TelephoneNumber
LastName
FirstName
MiddleName
Street
City
State
Zipcode
BirthDate
OperatorFlg
Comments

tblServiceTypes
---------------
ServiceTypeID auto-pk
ServiceDescription
PriceOfService

tblServices
------------
ServicesID Auto-Pk
CustomerID FK (Foreign key for Customer)
OperatorID FK (Foreign key for Customer)
DateOfService
ServiceTypeID FK (Foreign key for tblServiceTypes)
Results
PriceOfService

PriceOfService appears in the Service Types table and in the Services table. In the Service Type table, price will be the current price. In the Services table, price will be the price charged the customer which will default to the price from the service type table but may be overridden.

Before you build your database, take a look at the Northwind or Orders sample db's that come with Access. They are both order entry applications and work the way you will want your app to work. The samples have customer tables which you also have. They have a product table which is similar in concept to the Service type table that I added. The table that brings everyting together in the order entry applications is the Order/Order details tables. In your app, you will have a services table which is equivilent to the order details table. I toyed with separating services into services and services details. I didn't because I thought it might add more complication than value. However, if it turns out that you need information about the visit in general that shouldn't be repeated for each service detail record, I would recommend creating the table now rather than being forced into a big change later.

Once you understand completely how the order entry examples work, then go back to building your app.

More questions

Pat, please bear with me as I have less than a week experience with Access even though its been on my pc for a few years In order for me to get on with the project I will need some help clearing up some lingering questions that keep bothering me. When it comes to relationships, I would think that in the example you gave, that I would connect the same named fields like tblCustomers.CustomerID to tblServices.CustomerID and then tblServices.ServiceTypeID to tblServiceTypes.ServiceTypeID. Is this correct? The reason I ask is in some books or other samples I have examined, it doesn't seem like that is a hard and fast rule.

My next question would be on the OperatorFlg and the next closest match being OperatorID. Could you explain how that works?

And the last question is in my limited experience, I would think enforcing referential integrity would be needed with all its forward and backwards switches set. But whenever I try to use them I get error messages
Once I understand the rules on relationships I will be that much closer to knowing that when I complete a project, it may actually be reliable, or what good would it be
Thanks in advance,
sorebrain

added when edited: I think I need clarification on Foreign Keys and their creation. After some more reading I think I was making compound primary keys by using ctrl to select more than one field (thinking the added fields were the FK's). I am assuming now that the name and filetype are the important things for Foreign Keys? Or is it just the filetype? I read that the field name doesn't have to match but the filetype does. So for clarity I am assuming it would be best to name the fields the same and also of course, their filetypes?

tblServices.CustomerID ---> tblCustomers.CustomerID
tblServices.OperatorID ---> tblCustomers.CustomerID (to record this relationship, you'll need to add tblCustomers to the relationships window a second time because you are creating a separate relationship. This does not duplicate anything. Access will suffix the second instance of the talble with "_1" so the table names can be unique in the relationship window.)
tblServices.ServiceTypeID---> tblServiceTypes.ServiceTypeID

OperatorFlg

Click to expand...

- since I eliminated the Operator table and suggested that operators are equivilent to customers, you may want a was to identify which people in the customer table are operators too. The OperatorFlg should be defined as yes/no with no as the default. When you add an operator, set the value to yes. This will allow you to separate the two groups of people with criteria in a query if necessary.

I would think enforcing referential integrity would

Click to expand...

- yes, enforcing RI is critical to good design. You should also check the cascade delete option for the first relationship but NOT for the others. In the first case, if you delete a customer, you want all his services records to also be deleted. Cascade delete will do that for you. You will need to add your own business rule in the BeforeDelete event of the form that prevents operators from being deleted because that would cause all their related services records to be deleted. You don't want the operator record to control when the services are deleted, you want the customer record to control it.
If you are getting error messages when you try to enforce RI, tell us what they are. If the tables are empty you should not have any errors if the table keys are properly defined. If there is data in the tables already, it will need to have valid values in the foreign key fields or Access will refuse to create the relationships. You'll need to fix the data or delete it.

You don't do anything to specifically define a foreign key. When you create a relationship, "foreign key" is the description applied to the field in one table that "points" to the primary key in the related table. So the CustomerID in the services table is a "foreign key" and the CustomerID in the customer table is a primary key. So foreign, in this case, means exactly what you would expect. Something away from its "home".

The primary/foreign key pairs must have the same data types. However, autonumber is really a data type of long integer so that's what most foreign keys will be defined as. You can't have two autonumbers related to each other. It doesn't make any sense. So the pk will be autonumber for most of your pk's and the related fk's will be long integer.

As to names. I prefer to use the same name because I think it makes the relationships more obvious without having to open the relationship window all the time. There are exceptions though, and your app has one of those eceptions. Because I merged the operator and customer tables and both types of customer records are involved in a services relationship, only one of the columns in the relationship could be called CustomerID. The other needed a different name and so I used OperatorID which describes the use of the second customer record in the relationship.

Problem with enforcing RI

Pat, I have done all the editing and I cannot get referential integrity to work. I get the error msg "Microsoft Access cannot enforce referential integrity for this relationship" etc... I am trying to enforce it on the tblServices.CustomerID ---> tblCustomers.CustomerID which also has the second relationship tblServices.OperatorID ---> tblCustomers.CustomerID set as described in your last post. I double checked and have only PK's set as autonumber and all other fields with ID in them to Number. I have no data in the database and have even compacted and repaired to insure "0" starting autonumber fields. If I have followed your instructions correct I have 2 relationship lines from tblCustomers.CustomerID going to CustomerID and OperatorID in the tblServices. The only autonumber fields I have are not connected to each other.
Thanks in advance,
sorebrain

added: All relationships were set with no error messages.

Ahhhhh....I figured it out

Pat, I kept at it and figured out some stuff. I realized I had misunderstood your instructions and actually had 2 relationship lines drawn from the tblCustomers.CustomerID to the 2 different fields on the tblServices .CustomerID and .ServicesID and realized (but didn't know you could do that) that you meant to add another tblCustomers to the Relationships window. Then I decided to try using the auto-enable for relationships and of course it was already toggled on in the Tools|Options but wouldn't even draw a relationship line between the first 2 tables. So I did some more reading and rechecked all my settings. I realized that there were default values in the ID Number fields that were set at "0" so I removed all instances of the defaults in all the tables. Then when I went back and tried auto-enable it actually connected the 2 CustomerID fields automatically but not with the 1-N symbol for the referential integrity support. I edited the relationship and it allowed the referential integrity with cascade delete when I manually tried it. I was reading a book on Access 97 so I don't know if the automatic stuff has changed but anyhow, I made some progress and am learning as I go. Now to test the database so I know its gonna work I just need to add some records? And what is the final determination its gonna be stable or correct?
Thanks,
sorebrain