Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-24-2018, 06:01 AM   #16
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,369
Thanks: 128
Thanked 1,467 Times in 1,439 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Same table or new table

When I said all of them - that may have been a exaggeration - i didn't inspect every tables purpose.
Generally if you are storing data related to the practice then the practice ID should be in the child table as a FK, not the other way around.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-24-2018, 07:39 AM   #17
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,264
Thanks: 13
Thanked 241 Times in 239 Posts
Mark_ will become famous soon enough
Re: Same table or new table

You have three tables that may be redundant in actual use...
TblEmploymentType
TblProviderStatus
TblPracticeStatus

These look like types of lookups.
For myself, to avoid a LOT of headaches I always store these types of values in their own "TblLookups". That table has
LookupsID - Autonumber - PK
LookupType - Text - Hold a reminder as to what field(s) will use this lookup
LookupValue - Text - Holds the actual value returned from the lookup.
(OPTIONAL) LookupSort - Number - Numeric "Sort order" for when I want to arrange which values appear first.

This lets me have ONE table with several queries support all of my static lookups. This can avoid a lot of development issues when you need specific values for coding reasons that should not change. When I see "ProviderStatus" and "PracticeStatus" those almost scream to me "We don't change" and "The developer can do fun things with us".


While this does touch on normalization, it is also a development decision that can be very useful. I also have entries in my Lookups table to tell me what lookups I am using (the LookupType Combobox is driven off of a query on TblLookups).
Mark_ is offline   Reply With Quote
Old 05-24-2018, 07:46 AM   #18
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,844
Thanks: 55
Thanked 1,064 Times in 972 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Same table or new table

mpaulbattle, let me try to explain the type of decisions you have to make when you are going to normalize tables.

The first thing you need to do is to analyze what kind of entities you deal with. I'm going to take the issue of doctors and patients in a medical office as an example of the "right" line of questioning.

If you deal with people (doctors, patients, nurses, etc) that can be one type of entity or two (either all people, some of whom are doctors and some of whom are paients, or two entities to keep data separate). Deciding on one or two types of entity will depend on the business rules regarding the way the application must treat the two.

If patients have visits with doctors, the record of the individual visit is another type of entity. The idea that a doctor has a "practice" means that you have a third type of entity. Some of the actions a doctor could perform are yet another entity, perhaps a "treatment." In brief, different entities each get their own table based on commonality of business model rules.

It is possible for these entities to be directly and indirectly related to each other. It is possible for multiple relationships between entities. When doing the data analysis, what you look for is the nature of the relationship.

For instance, if a patient visits a doctor, you have the "visit" which requires both a doctor and a patient. So that is a double-barreled dependency. You can have a patient and you can have a doctor whether or not they see each other, so the patient and doctor are independent entities. But the visit requires BOTH patient and doctor, so it is a dependent entity. The treatment occurs in the context of a visit, so treatments are dependent on visits. The doctor and patient can see each other many times (sort of the definition of a practice, I believe), so you can have a many-visits-per-patient and ditto for the doctor. Thus, you have a pair of many-to-one relationships. Which also means that each visit is a separate entity from other visits. So you need something about a visit to tell two visits (same doctor, same patient) apart, perhaps such as a date or a sequence number.

What good is this kind of discussion? Well, the entities that are dependent on something else higher in the hierarchy of your structure are the ones that must have foreign keys (FK). The things on which they depend are the ones that must have prime keys (PK). So this kind of question helps you decide where the keys will go and how they will be used. It IS possible to have both a PK and some FKs associated with the same entity.

So the next kind of question that you must decide is, what data goes where? Sometimes it is easy; sometimes, not so much. For the doctor-patient-visit-treatment structure, the doctor entity is described by a doctor table. The information about the doctor might include phones, addresses, certifications, etc. Things where the doctor only has one (such as "home address") might be in a Doctors table. Specialties or certificates, of which the doctor might have many, would go in a child table with a one-to-many relationship with the doctor.

The idea of a Practice depends on how you define it. If you have multiple doctors sharing a practice, a question would be whether they share patients as well, or whether the doctors only share offices and business staff but are exclusive to a given patient.

These are the questions you would ask when normalizing so that you can find the right number and content of tables. I know it can be completely confusing and frustrating - but if you have a good understanding of your business model, you should be able to get a good data layout quickly.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-24-2018, 11:16 AM   #19
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

Quote:
Originally Posted by Mark_ View Post
You have three tables that may be redundant in actual use...
TblEmploymentType
TblProviderStatus
TblPracticeStatus

These look like types of lookups.
For myself, to avoid a LOT of headaches I always store these types of values in their own "TblLookups". That table has
LookupsID - Autonumber - PK
LookupType - Text - Hold a reminder as to what field(s) will use this lookup
LookupValue - Text - Holds the actual value returned from the lookup.
(OPTIONAL) LookupSort - Number - Numeric "Sort order" for when I want to arrange which values appear first.

This lets me have ONE table with several queries support all of my static lookups. This can avoid a lot of development issues when you need specific values for coding reasons that should not change. When I see "ProviderStatus" and "PracticeStatus" those almost scream to me "We don't change" and "The developer can do fun things with us".


While this does touch on normalization, it is also a development decision that can be very useful. I also have entries in my Lookups table to tell me what lookups I am using (the LookupType Combobox is driven off of a query on TblLookups).
I will have to look into lookups. I saw this when I used the Analyze Table but I didn't understand it, and plus it separated fields i believe should have been put in the same table, i.e. emobile, emobileactivationdate, emobilecode. All three were in different tables so it didn't seem right to me. I will research this too. Thanks for reply.
mpaulbattle is offline   Reply With Quote
Old 05-24-2018, 12:59 PM   #20
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,264
Thanks: 13
Thanked 241 Times in 239 Posts
Mark_ will become famous soon enough
Re: Same table or new table

Before you go into too detailed of a search, there is a fundamental question you need to ask for each field you are going to use a lookup on; "Should its value change if the table used for the lookup changes?"

In most cases, yes you will want changes to replicate. In some cases you will want to retain data that is now "Obsolete", so you will either need a flag in the table for lookups saying "This record is no longer used" OR you will need to store the actual value in the record rather than a pointer to the value in the lookup table.

For some specific business rules you do actually save the value. This is normally because there is a regulatory or auditing reason to save EXACTLY what was there rather than a pointer to something that could be changed.

Just keep in mind that you have "By reference" and "By value" lookups. By reference is the normal "Keep a copy of the PK for the looked up value" while by value is "Copy the value from the table I'm looking it up from". This will help avoid some confusion if you start reading about one or the other.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
cross-table query not shown correctly in table from make-table query killerflappy Queries 5 10-09-2017 10:53 PM
Populate table name in sep created table, each time a table is added snoopydoopy Queries 1 10-23-2014 04:43 AM
form for table A to search fields from table B and populate into table A johnseito General 16 08-06-2013 06:25 PM
Relationships between table - 3 fields in 1 table related to another same table laiching Forms 2 01-16-2012 06:20 PM
Naming a table created with a make table query from a field in the table? AllanN General 4 07-03-2002 04:52 PM




All times are GMT -8. The time now is 12:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World