Database Normalization Opinions (1 Viewer)

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
jz, in your example you used a series of combo boxes for the events, but can this be changed to input through textboxes instead?

If so, then how would it look?

I dont want a user clicking on the wrong event for the date tracking.
 

jzwp22

Access Hobbyist
Local time
Today, 07:41
Joined
Mar 15, 2008
Messages
2,629
Theoretically, yes you can use a textbox. But let's say your boss wants you to evaluate some performance metrics. For example, perhaps the number of days from when a case is initiated to when it is submitted to the director is important. Let's say you allow you users to enter the event via a textbox, you may get the following entries over various cases:

Sent to Director Mr. Big
Sent to Mr. Big
Sent to Dir Big
To Dr Big
Snt to diretor Big

Now how are you going to conduct a search to find the "sent to director" event based on the above values?

Using a combo box with predefined text values enables standardization of data which facilitates searches/queries and contributes to the integrity of the data. Additionally, having the choices available in a combo box saves your users time since they do not have to type in anything.

There are ways to control what a user may select from a combo box or which events are shown in the combo box. For example, if there is a specific sequence to the events that occur related to a case, you could add a sequence number field to the events table and then use that to verify that an event is not added prematurely based on events already entered or you could use that sequence number to filter the combo box and show only the next sequential event. Or you could even add the next sequential event automatically when a new record event is added to a particular case that is progressing through the sequence of events.
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
jz,

I should have clarified, I meant date fields and not text boxes. If you look in my example db that I posted earlier, you will see the tab "Time Line" where the users input dates into the date fields via the date picker.
 

jzwp22

Access Hobbyist
Local time
Today, 07:41
Joined
Mar 15, 2008
Messages
2,629
The date for each event would be entered via a text box not a combo box as I indicated in the tblPSUEvents. The event information would be entered via a combo box

tblPSUEvents
-pkPSUEventID primary key, autonumber
-fkPSUID foreign key to tblPSU
-dtePSUEvent (date field)
-fkEventID foreign key to tblEvents
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
I apologize for my ignorance.

I see what you are saying about the dates.

Let's say you allow you users to enter the event via a textbox

In my case, the user never will input the event, only the dates for each event.

I am sorry that I am not getting what you are trying to explain.
 

jzwp22

Access Hobbyist
Local time
Today, 07:41
Joined
Mar 15, 2008
Messages
2,629
It sounds like you want to append the events when a case is created so the user only needs to enter the applicable date for each. Is that correct?
 

RainLover

VIP From a land downunder
Local time
Today, 22:41
Joined
Jan 5, 2009
Messages
5,041
magster06

I looked at your PDF but not your Databse. I use Access 2003.

Other than the fine advise you have been given I would suggest that you look at your Naming Conventions or should I say lack thereof.

In my Signature there is a Link that if followed will allow you to download an article on Naming Conventions. It is worth considering.

Your original design is more like grouping like things together into different Tables rather than separating Repeating Data, which is what Normalisation is all about.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Sep 12, 2006
Messages
15,613
personally, I think that before you consider the individual fields (datums), you ought to start at a level higher. Just think of the OBJECTS you are trying to model.

design the table structure that they fit into. Then add the fields you need.
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
Again, thank you everyone for taking the time to help, even though I think I may be a lost cause.

I have attached a new pic of my tables. I know not much has changed, but I have changed the naming conventions for the better (I hope).

Also, added the fk where I did not have any before.

I am hoping to accomplish the following:

1. Enter a callers details via a form (which will include 3 different tables) and this form will be data entry only.

2. Recall the info into another form so the user can add, edit, delete records.

The form will have one more tab (Focus) so that the user will input into 5 tables. 2 of the tables, the user will input through a subform. The other 3 tables will receive input from the main form.


Then there is the add, edit,... form.

I now know I did everything backwards. I finished all the forms and design before I created a somewhat table relationships. I am still learning.
 

Attachments

  • PSUalmostRelationship.jpg
    PSUalmostRelationship.jpg
    97.5 KB · Views: 116
  • InitialCallform.jpg
    InitialCallform.jpg
    95 KB · Views: 129
  • PSUeditForm.jpg
    PSUeditForm.jpg
    94.1 KB · Views: 123

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Sep 12, 2006
Messages
15,613
here's a few general thoughts. you really need to stop and reconsider, I think.

you can't really assume your statement 1 until you have the datadesign completed. why exactly 3 tables? In any event, you can only update one table with a form at one time. A form is really based on a single table

the form may use a query that takes that table, and links it to other tables for information purposes - but at the heart of it. it is linked to 1 table.

what you are doing is like designing a house by starting with the bathroom layout, without even considering how the house will be laid out in general.


so - what exactly are you trying to model. Legal Cases? what people are involved in these cases. what data do you need to manage. do you need to store details of multiple meetings related to the cases? etc etc. use all these thoughts to decide on the nature of the system that you want to model. then begin to add the data elements (fields) to the tables in the structure. if you run into problems, refine the design. don't think about the actual user interface, and form layout.


make sure you understand every nuance of the system. the worst problem is users assuming that you knew something obvious as far as they are concerned.

reminder - don't base the analysis on a preconception about the user interface at the beginning. it's immaterial. just get the data design right, and the interface will become much easier. There is a lot of serendipity anyway. users will find ways of using your system, or make requests you never even thought of, once they start using it.

is this just for fun, or is it a work task? can you get some professional help with the design? the right design will save you hours of pain and recoding/reanalysis down the line.

anyway - good luck with your project.
 

RainLover

VIP From a land downunder
Local time
Today, 22:41
Joined
Jan 5, 2009
Messages
5,041
You show only 5 Tables in your relationships pic.

Where are the others.

BTW in tblCases. CaseID should be your Primary key. I assume it is Autonumber. This would then change all your Foregin keys to numbers..
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
In any event, you can only update one table with a form at one time. A form is really based on a single table
I did not know this. The weird thing is up until 3 days ago I was able to update 3 tables from the single form (using inner joins), it finally stopped working when I tried to add tables 4 and 5, lol.

is this just for fun, or is it a work task? can you get some professional help with the design?
This is for work and unfortunately we have zero professional help available. It is just me, myself and I at the helm and this ship is sinking fast, lol.

I agree with you gemma, I definitely put the cart ahead of the horse. I was so into the form design and realized too late that I messed up.

BTW in tblCases. CaseID should be your Primary key. I assume it is Autonumber. This would then change all your Foregin keys to numbers..
I forgot to remove the CaseID field. I want the CaseNumber as my pk in the main table.

The pics of the tables in the relationships show all the information needed for my db. I guess I just thought that a form with tabs was the way to go, but I am now rethinking that one as well.

You show only 5 Tables in your relationships pic.
I left out the document table, sorry

Your original design is more like grouping like things together into different Tables rather than separating Repeating Data, which is what Normalisation is all about.
Rainlover, so repeating data as the addresses? like jz pointed out in an earlier post?

I know you guys (most of you) probably do this for a living and it comes very easy to you, but it does not for me. I sometimes struggle to grasp what you are trying to convey to me through these post.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Sep 12, 2006
Messages
15,613
when you say "update 3 tables from a single form" - what sort of values are you trying to change in those tables?
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
when you say "update 3 tables from a single form" - what sort of values are you trying to change in those tables?

I must be tired gemma, I meant that I was able to input new data to 3 different tables with the one form.

I think I am still going with a tabbed form, but I will use subforms to input and update data into the different tables.
 

RainLover

VIP From a land downunder
Local time
Today, 22:41
Joined
Jan 5, 2009
Messages
5,041
You show only 5 Tables in your relationships pic.

Where are the others.

BTW in tblCases. CaseID should be your Primary key. I assume it is Autonumber. This would then change all your Foregin keys to numbers..

The Relationships Pic you posted about 30 Tables. They should be part of your relationships.

CaseID as Primary Key and of type Autonumber is based upon years of experience and many mistakes. But if you want to ignore the advise then that is your prerogative.

As a big positive your form design is good. They certainly look better than my designs. I do not have an artistic bone in my body.
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
Rainlover,

The Relationships Pic you posted about 30 Tables. They should be part of your relationships.

The other tables are ones that I use to populate my combo boxes. I thought that they could left on their own and not linked to other tables. I will change this.

CaseID as Primary Key and of type Autonumber is based upon years of experience and many mistakes. But if you want to ignore the advise then that is your prerogative.

I am not ignoring any advise; trust me, at my experience level, I need all the help that I can get.

I was not quite understanding the auto number as a primary key. I was trying to use a unique key to link my tables, such as the "Case Number". I will change this.

As a big positive your form design is good. They certainly look better than my designs. I do not have an artistic bone in my body.
Thanks, at least I have come close to doing something right, lol.

Like I mentioned, most of the forms are complete. I just need to get my relationships pulled together and then adjust the forms to match them.

I found a model (thanks to bob) and I am hoping that I can adjust it to fit my needs. I will post back with the updated relationships as soon as I can.
 

RainLover

VIP From a land downunder
Local time
Today, 22:41
Joined
Jan 5, 2009
Messages
5,041
True, it is not overly necessary to link all the LookUp tables in your Relationships.

But imagine this. Create the joins and lay all the tables out so that all the relationships are clear. As in tidy. Then print this out on A3 if you can. Your local print shop will do it for you if you put it on a stick as a PDF. Then you have your MAP which you can pin to the wall and refer to it as required.

Another advantage of creating the relationship is to enforce referential integrity, which you can't do without first creating the join.
I and most others but not all use Autonumber as the Primary Key in all tables. Natural keys like your SSN have been proven not to be unique. A Number can always be unique.

Did you look at my naming conventions as suggested earlier. If not it is an easy read and if followed would help you better understand. This convention is mine. Not perfect but at least I have one. Create your own if you like, as long as others can understand.

Post back if I missed something.

BTW. Have a look at the article from Microsoft on Normalisation. It is relatively simple compared to many other writings.
 

magster06

Registered User.
Local time
Today, 07:41
Joined
Sep 22, 2012
Messages
235
Hey guys,

I have changed some things around and wanted to see if I am headed in the right direction.
 

Attachments

  • NewPSURelationships.jpg
    NewPSURelationships.jpg
    95.7 KB · Views: 121

RainLover

VIP From a land downunder
Local time
Today, 22:41
Joined
Jan 5, 2009
Messages
5,041
tblAddresses.

City is repeating Data. It should be in a separate table.

tblCities

CityPK Autonumber
City Text
State Text
Zip Text

You could put State into yet another table. I would, but most people don't. Technically speaking it does belong in a separate table. It is not a train smash if you don't.

I just wanted to demonstrate how to apply normalisation. See if you can apply this against the other tables.
 

Users who are viewing this thread

Top Bottom