normalization question - Break the rules? (1 Viewer)

[chewy]

I have a db that has a list of customer names for check processing. I have the customer name in a sererate lookup table for the main form. On the main form however I have the customer name (which is looked up fro the customer name table) and a customer # (which is not inthe customer name table). I would put it there normally but, some customers have more than one customer number for whatever reason. So is this a good time to break the rules?

BTW I have no control over the customer numbers that the company assigns.

 

[Jack Cowley]

tblCustomerNames
CustomerID (PK and autonumber)
LastName
FirstName
...other fields..

tblCustomerNumbers
CustomerNumberID (PK and autonumber)
CustomerID (FK)
CustomerNumber

That is how I would do it....

 

[The_Doc_Man]

Jack is right. Particularly for a database involving financial data for customers or clients, you really don't want to break the rules.

Break the rules for the database you use to catalog your personal collection of comic books.

Break the rules for a recipe database.

Don't EVER break the rules where someone else's money is involved.

 

[chewy]

cool. Thanks for the suggestions guys. They make sense.

 

[chewy]

how do you set a foreign key? Or I should say how would I relate those tables to use them in a lookup for them?

Thanks

 

[Jack Cowley]

Generally you would have the CustomerNumbers table as a subform of the form that has the Customers so you can add or see their current numbers. For a lookup create a query using the two tables.

hth,
Jack

 

[Pat Hartman]

A foreign key is established by definition when two tables are joined using the relationship window. When you join CustomerID in tblCustomerNames to CustomerID in blCustomerNumbers, CustomerID in blCustomerNumbers becomes a "foreign key" to tblCustomerNames. "Foreign" is used in the standard English language sense. The "foreign" key is what the "native" key of a table is called when it is stored in some other table to form a relationship between the two.

 

[chewy]

OK. I understand what you are saying but I still dont know how I would use the index' to lookup the customer name in another table. This is a step I have avoided in the past because I did not understand it.

If anyone would be so kind as to show me how this is done. The database is here

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=46176

I appreciate any help I can get in this important step I need to understand database design even more.

 

[Pat Hartman]

orders, northwind, and solutions have very good samples of lookups. You might try opening them and looking at how they work. There is a tutorial that goes with northwind that explains everything.

 

[chewy]

I looked at the Northwind DB. See I understand how to use lookup tables fine by just picking data from a table. However I dont understand how to only get the customer numbers associated with a specific customer

Any help?

 

[Jack Cowley]

Can a customer have more than one CustomerNumber? If not, then CustomerNumber should be in the CustomerTable. If they can then use a query based on your two table where you select CustomerID and CustomerName from the Customer table and CustomerNumber from tblMain.

I hope that is what you are after...

Jack

 

[chewy]

yes they can have more than one customer number. That is what I don get. But I will try your suggestion of a query.

Thanks!

 

[chewy]

should that query be used off of the table or should I base it in the Form?

 

[Jack Cowley]

Queries can only be based on other queries or tables. Forms are just 'windows' on data and do not contain any data.

I am not sure what you mean when you say you don't understand why a customer has more than one number. It is your database so one number per customer is adequate. I am not sure what is going on, but your table as a field called CustomerNumber but that seems redundant to me....

Jack

 

[chewy]

see my first post for a description of what is going on