Primary Key vs. Multiple Users

[jamason07]

Good Afternoon Everyone,

I brought this up once before but I heard from a dbase guru that having multiple primary keys on your tables could prevent you from having multiple users accessing your dbase even if the dbase is split. Anyone have any insight on this? I have a recently created dbase that is pretty complex and regardless of what I try, I can't get multiple users on at the same time. I've compared it with others I've created that allow several people on and can't find the difference preventing the access. Thanks!

JAM

 

[rdodge]

I haven't heard about that issue in particular, but regardless, it is best practice to have only one field set as primary key in each of the tables as this gets into the 5 rules of data normalization form (1NF...5NF) For more information on data normalization form, you can go to:

The first 3 normalization rules:

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

The other 2 normalization rules:

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm8.html

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[JeremyNYC]

The last two rules of normalization are pretty much considered useless to most day to day data application. I've been doing databases for seven or eight years and I wouldn't even try to describe those two rules for you.

Do you mean a compound primary key? Because you can only have one primary key per table (_primary_). But it can consist of multiple fields. In a link table, one that serves to create a many-to-many relationship between two tables, the most sensible thing to do is have the two foreign key fields form the compound primary key for the table, even if, as is sometimes the case, there are other fields in the table.

But there's no way this will prevent multiple users from getting at your tables. My thought is your dbase guru was getting into some very particular, very funky territory.

It's weird that you can't get more than one user on a database at a time. There's no reason for this, unless you're opening the database in exclusive mode. It's not like there's someting you have to "do" to an Access database to make it usable by more than one person. That's just the way they are. Are you sure you've checked on whether or not your opening the database exclusively?

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.

 

[jamason07]

Jeremy,

Thanks for the info. I have checked the exclusivity and it is set for multiple users. I did clean up my tables and made minimal use of primary keys. My other fix has been to delete the access created switchboard. Once I created my own switchboards, I was then able to put multiple users on. I think it had something to do with the vb code created around the switchboard and entry into the main form....but I don't know exactly what. But anyway....it is working fine now.

JAM

 

[rdodge]

One thing to be sure, you have checked in the DB advance options, the db will default to open in shared mode.

as far as the last 2 rules of normalization form, yes, these 2 rules are normally not needed, but there are times when they do need to be applied. However, for them to be needed, it normally would require that you have some complex situation, which would otherwise deal with multiple fields.

The 4th rule is relatively easy to explain as it deals with many to many relationships. Let's use employees and projects as an example just as the links use.

An employee could be assigned to many different projects while each project could have many different employees assigned to it. This is a such situation that creates a Many to Many relationship. The only real way to resolve this issue to to create a third table, which will contain the employee number, the project number and could contain a unigue ID number, keeping to the 2NF rule.

By doing this, you can have a single project number return a list of employess on that project, and the same thing with taking an employee number and return the project id numbers that the employee is assigned to.

This rule helps in saving a lot of DB space that would otherwise be wasted by having all of the project tasks listed multiple times, should the employee ID number be entered one time per record, and this also allows for the fact, you don't need to have X number of fields to accommodate the x number of employees. Look at phone numbers per person, phone numbers use to be only like 2 or 3 per person, but it's now like 5 phone numbers per person. Think of all the coding that would have to be done to just add 2 or 3 additional fields to each person's record for this. As they say, adding records is cheaper than adding fields or columns.

the fifth rule deal with Many to Many to Many + relationships. Now, I'm not going to get into that one as it's rarely ever needed, not to mention it's a bit harder to explain it, but again, there are those few very rare times when it's needed. Main thing this last rule basically deals with, not only can you drill down, but you can also drill up without losing the data integrity via ambiguity. If this rule was to be kept simple, it's still a lot like the 4th rule, but only you are dealing with more than 2 tables.

Just so you know, I do use all of these rules in my DB creation. Most times, I only need to focus on the first 3 rules, but there are times when I also need to focus on the last 2 rules like in my case, I'm in the process of creating an MRP2 system (MRP = Master Resource Planning, MRP2 = Materials Requirement Planning), so as it can automatically do the daily scheduling of jobs based on the various availabilities, restrictions, processes, and time lines.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000