Am I using Access Correctly?

[MakinBacon91]

Hello All,

I am new to Access, and I am starting to figure things out, working up to designing a database for my company.

I am wondering if I am going in the right direction, or if it is plainly obvious to someone more experienced, that I will have problems down the road.

Here is a screenshot of my Relationships View: Pasteboard — Uploaded Image

Let me know if I need to explain anything to help you understand what I am going for.

Thanks,
Bacon

 

[Joe4]

In creating Access database applications, it is important to have a good grasp on Relational Databases and the Rules of Normalization. Otherwise, you can program yourself into a corner really quick, and create a ton of headaches for yourself. So if you are not already familiar with these concepts, I would recommend educating yourself first before diving in.

Here is one good article, there are many others that can be found with a Google Search: http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf

 

[MakinBacon91]

In creating Access database applications, it is important to have a good grasp on Relational Databases and the Rules of Normalization. Otherwise, you can program yourself into a corner really quick, and create a ton of headaches for yourself. So if you are not already familiar with these concepts, I would recommend educating yourself first before diving in.

Here is one good article, there are many others that can be found with a Google Search: http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf

Thanks for the reply Joe!!
The document you linked has been very helpful.
I understood 1NF of Normalization, but 2NF and 3NF have helped me out a lot in understanding how to structure the database!

 

[HiTechCoach]

I have yet to see a properly normalized database that needed composite (multiple field) primary keys. They can make relationships and keeping referential integrity very difficult in a hurry.

I have found the using a single field primary key that is system assigned (auto number) will make your life much easier.

 

[MakinBacon91]

I have yet to see a properly normalized database that needed composite (multiple field) primary keys. They can make relationships and keeping referential integrity very difficult in a hurry.

I have found the using a single field primary key that is system assigned (auto number) will make your life much easier.

Thank you for the reply Coach!

Like I said, I am new to Access. In fact, today will be my third day of learning/practicing the art of database design. Until late yesterday, my mindset on using an (auto number) was, "why would anyone use auto number, when you could use descriptive names instead?" The breaking point was when I read the link posted by Joe4 about normalizing forms, and I realized that if I didn't use (auto number) that half of my tables would all have 2-4 fields required to link them together.

I am still not sure if my design is efficient, but I am definitely heading in the right direction.

 

[Kyle123]

I agree with Boyd, however composite keys are useful for M2M link tables - I wouldn't use an autonumber key there.

I'd usually also prefer an autonumber for a primary key, but there are scenarios where GUIDs offer advantages.

 

[MakinBacon91]

I agree with Boyd, however composite keys are useful for M2M link tables - I wouldn't use an autonumber key there.

I'd usually also prefer an autonumber for a primary key, but there are scenarios where GUIDs offer advantages.

GUIDs?

 

[Kyle123]

Sorry, not relevant to your application, a Guid is a globally unique identifier - something like {3F2504E0-4F89-41D3-9A0C-0305E82C3301}

 

[MakinBacon91]

Sorry, not relevant to your application, a Guid is a globally unique identifier - something like {3F2504E0-4F89-41D3-9A0C-0305E82C3301}

Okay, thanks for the followup!

 

[HiTechCoach]

I agree with Boyd, however composite keys are useful for M2M link tables - I wouldn't use an autonumber key there.

I still use a single field with a system assigned value (like auto-numnber) and the primary key. I will create an index that has the foreign key fields set to no dups.

I'd usually also prefer an autonumber for a primary key, but there are scenarios where GUIDs offer advantages.

Whay make a good primary key is that it is something that is system generated. That can be an auto numbner , a GUID, or something else. It is something to uniquely identify the record. It is not data.

@Bacon,

In most applications the end user never see the actual primary key. It is usually hidden since it is normally meaningless data to the user.

You can have create an index on a field that is net to no duplicates. Like a product UPC code. For the user this would appear as the unique identifier. To the database it is not.