Database Structure 2

[fredka]

I have a database that tracks several different types of activity for an outside sales force. I have a table named "tblMasterBrokers" In this table we have the broker names and a brokerID (a 6 digit number that we use to identify the broker)

In the associated tables where I track the activity (sales for example) should I use the brokername or the broker?

I am guessing that I should probably use the broker number? If that is correct, on the data entry form to enter the activity, how could I show the master broker name but have the broker number entered in the field?

 

[lespaul]

when you create a combo box using the wizard, you are prompted to answer questions that set this up for you. Basically you have a query that is the source of the combo box that contains both the ID and the name. The display of the combo box is set to only show the name but updates use the ID.

Read the documents linked below for more information on ForiegnKeys (which is what BrokerID would be in the other tables) and joining into tables to just get the information you're looking for.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fredka]

Thanks so much for your help!!!

So you would suggest using the brokerID as the foreign key rather than the name?

Fred

 

[lespaul]

yes, because a name can change. What happens when Jane Doe gets married and her name is changed? If you have an "ID" field, all you have to do is change her name in the Broker table and now it's changed everywhere. If you store her name in other places, you now have to change it in every location.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fredka]

Thats what I figured - the only problem is that they change these broker id's occasionally also.

thanks so much!!!!

 

[lespaul]

If someone gets a new broker id I would add a new record, not change the old one.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[JoeAtWork]

Instead of using BrokerID, make a new ID field the primary key (probably an autonumber would be best). There's nothing that says a primary key has to be something that the user sees.

In fact it is usually better for your primary keys to have no special meaning to your users.

 

[fredka]

Now I am starting to see where I have bad structure.

Just one question. I currently have a "tblBroker" that has the primary key set as the "BrokerID" and "SecondaryBrokerID" (these broker id's are numbers that our company assigns to the broker - the reason we have two is that we may want to track one broker seperately)

my question is: If I change the primary code from what I have now to an autonumber, how do I ensure that a duplicate combination of brokerid and secondarybrokerid are not entered into the table?

Thanks!!!

 

[lespaul]

you can add an index to the table that doesn't allow duplicates in a combination of fields. Go to the table design view, select the two fields and add an index with the 'No duplicates' selected.

If you still can change the structure, be sure to read the fundamentals document linked below and verify that you've reached the proper database state of normalization.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fredka]

thanks for your help leslie - this was one of my first databases that I created - I still have a lot to learn but have come a long way since then.

thanks again!!!

 

[fredka]

Just one more thing that I am struggling with - When I am looking at data in table view now, I have a bunch of numbers instead of meaninful data - I am used to going into a table and making changes directly in the table.

Now that I am using foreign keys in the data table instead of the actual name.... when I look @ the table directly, those fields do not make any sense to me.

In the past if I wanted to view all of the data for a specific masterbroker I would just go in and filter that borkers name. Now that will be a number and not as easy to translate. How do you handle that?

I know from a database structure standpoint this makes sense to do - what I am struggling with is now the database is more complicated to view. ??

Thanks!!!

 

[lespaul]

you create a form to do the data entry. There's a wizard that will help you design the basic ones for adding and editing the broker information.

Leslie

 

[fredka]

I guess I just need to shut up and grasp the concept.

Thanks!!!

 

[jadams0173]

In the past if I wanted to view all of the data for a specific masterbroker I would just go in and filter that borkers name. Now that will be a number and not as easy to translate. How do you handle that?

Make a query to view the data with joins on the PK and FK. Select the fields to "put the data back together" with the name and broker ids, etc. in the query. You can still view what you need to and put criteria for the specfic data or individual you want to view. JMTC


I tried to have patience but it took to long! -DW

 

[KornGeek]

In fact it is usually better for your primary keys to have no special meaning to your users.

I disagree. If you have a field that is guaranteed to be unique and unlikely to change, I prefer to use this as a primary key. Even though it is a small amount, there is no reason to waste space in every record if a perfectly good key already exists. By having a primary key that has some meaning attached, debugging your tables, queries, forms, etc. can become much easier. In some cases, there is no good key available (and this case very well may be one of them), and it becomes good to assign an ID to each record. However, generally speaking, I prefer to use meaningful keys.

Also, be care with Access's autonumber fields. I have personally encountered cases where the database (for no known reason) resets the pointer and starts trying to add autonumbers that have already been used. Also, I have read of other problems that can occur, especially in multi-user databases. MichaelRed wrote a good FAQ about how to implement your own autonumber system. I've used it and modified it, and it works wonderfully.

 

[JoeAtWork]

Korngeek, here are my counter arguments.

First of all, it's very rare to find a meaningful column that is unique and unlikely to change. For people (e.g. customers) the most likely candidate is the SIN. Unique all right, but what if the customer refuses to give it (which they have every right to do). Now your data entry person is stuck - they will most likely try something like 999999999 - which will only work once.

Same trouble with serial numbers for parts - works fine - until you get to the scenario where that meaningful information is not known (example: it's become illegible).

In the example of the original poster, he was getting his IDs from another program. This is especially a case were I would not rely on the "uniqueness" of the column - I am at the mercy of an external program that I have no control over. Maybe in the future the business rules for that program will change, so that it is OK to have duplicate IDs.

Also, if it's meaningful, it's something the user will see. Users have this nasty habit of wanting to be able to change what they can see. It's usually not a good idea to let users arbitrarily change primary keys. Better to keep the primary keys hidden from the users, which can only be done if the values are meaningless to them.

In short, using keys that have no "real world" meaning is the best way to guarantee their uniqueness and stability. You are not at the mercy of external forces, including (and maybe most importantly) the whims of your users. Let them be blissfully ignorant of what binds their database together.

The amount of space used for the primary key is completely negligible - it's not really an argument.

As for debugging, I can't think of a case where it would have been easier for me to debug when an ID was something discriptive (e.g. CarMakeID="Honda" vs. CarMakeID=5). In a normalized relational database, if I would need to see the car was a Honda, I would just include the CarMake column along with the CarMakeID (please don't tell me I'm wasting space - that argument doesn't make sense in this day and age - are we still using two-digit years?).

I've heard of the autonumber problem, but in almost 10 years of working with Access databases that is the one problem I have never come across. If I recall it is related to some sort of compact & repair issue. Perhaps for the "expert" user MichaelRed's function (or the similar one in Getz's Handbook) should always be used - but for the amateur (who we often cater to in these forums), telling them to use the autonumber feature is about as in-depth as you want to get with them. Telling them to use anything less automatic will get them in trouble (because the scenarios I have described above will not be so apparent to the layman).

 

[fredka]

thanks for all of the great replies. This was my first database and is about 5 years old. In my case, the broker number does not change often but it does change.

I had a hard time feeling good about changing the primary key from the broker number to a seperate primary key. the main reason is what korngeek is saying about the familiar field in the table. Now, I can't just look in my table and understand what some of the data is... will need to link the tables up. Not a terrible thing but after years of having the meaningful data in the table, a bit of a change.

I decided to use a seperate primary key but not an autonumber - I wanted to use 4 digits and start each tables primary key with a different number (for example, the brokers primary key start with 1 (1001, 1002) another tables primary key starts with 2 (2001, 2002)

This restructure became a great learning experience because all my queries and forms needed to merge more than just one table together.

Thanks for all the great advice!!!

Fred

 

[JoeAtWork]

Now, I can't just look in my table and understand what some of the data is

I just can't understand the scenario where that would be useful to me. If I am looking directly in an Orders table, I can't think of a reason I need to know that CompanyID=33 means "Ikea". I can't see the extra benefit if CompanyID="Ikea". If I'm looking for this type of information, I would create a query that joins the Company and Orders tables together.

When I am thinking about design aspects such as ensuring uniqueness of the record, and relationships to other tables, I never consider what an individual record in the table looks like if opened directly. Why should I - that is a UI concern. In my forms and reports, I will see "Ikea" because my form/report is based on a query. Primary keys and table relationships are at a higher level than individual records.

after years of having the meaningful data in the table, a bit of a change

You will still have the meaningful data, they just won't be primary keys.

I decided to use a seperate primary key but not an autonumber - I wanted to use 4 digits and start each tables primary key with a different number (for example, the brokers primary key start with 1 (1001, 1002) another tables primary key starts with 2 (2001, 2002)

This will work, although I don't understand why go through the effort of making the first number "mean" something.


I have thought of one scenario that I sometimes use primary keys that are also displayed to the user. For things like Invoice Numbers, Order Numbers, etc., since the real life scenario is that the number must be unique and they must exist, it matches up with the requirements of a primary key. Also, it's a value that users would expect to be auto-generated and would not be able to change themselves.