flat or normalised? (1 Viewer)

[neilwebber]

Hi All
First - hope I'm in the right forum here.

I know that normalisation is already well covered here (not least by Pat) but I can't find anything that addresses my particular question.

I have a large db (50-60 fields, mostly Yes/No).
Field 'PCLNo' is PK and represents a geographical location. All other fields contain info relating to that specific location.

The info is presented on several tabbed forms and each form must cycle thru with one record = one 'PCL Number' ie the user always wants to find the site (PCL No) and then view or edit the info for that site.

There are some 'repeating groups' - PA1, PA2 etc (see Pats 1st book extract) so I know that normally I should split these from the big table and have PA1 etc as PK and PCLNo as foreign key. However, how do I then put these details on a form so that they all appear under 1 PCLNo record rather than the form cycling thru each PA? Is this what subforms are for?

Basically what I'm saying is if I split the table into several smaller ones I'd still always want PCLNo as the PK as every other piece of data always relates to this number, and would have a series of 1:1 relationships. So, is it worth normalising or should the db remain flat?

I should say that the db is used for viewing and update only, no analysis or calculations are performed and is very rarely searched - users generally know what 'PCLNo' they are looking for.

Apologies if I haven't expressed this very well - I'm probably missing something obvious but I've been thrown in at the deep end somewhat (management eh?)

thanks

 

[3699wat]

I wonder why you want to split up the table if you "only" have about 60 fields. There is no immediate need to do that.

 

[Pat Hartman]

When you split the table, you should end up with several 1-to-many relationships, NOT several 1-to-1 relationships. The repeating groups end up as some number of rows in the new table not some number of columns. The key to the many-side table will be comprised of two columns - PCLNo - and some code value to identify what data that row contains. Let me take a simple example. You have a spreadsheet that contains forcast information. There is a row for each expense type and a column for each month. Each year is probably a separate sheet or workbook. The normalized table will contain three columns but many rows. The colums would be ExpenseType, ForcastDate, and ExpenseAmt.

 

[neilwebber]

Thanks to you both for your replies - after a bit of thought I've decided that I have to go with Pat's suggestion (she is the Guru after all).

I've mapped out a possible structure for my tables and their relationships but not confident that I've got it right - any comments are much appreciated.

I now have four tables

TblMain with fields-
PCLNo (PK)
various others
(A geographical location and various other details about it)

TblPABasics with fields-
AppNo (PK)
PCLNo
various others
(many Planning applications on the site and various other details)

TblConsult with fields-
ConsultationNo (PK)
AppNo
PCLNo
various others
(many Consultations for each Planning app on the site)

TblSiteSC with fields-
SSCNo (PK)
AppNo
PCLNo
various others
(many Conditions for each Planning App on the site)

TblMain is linked to TblPABasics via PCLNo (1 to many)
TblPABasics linked to TblConsult via AppNo (1 to many)
TblPABasics linked to TblSSC via AppNo (1 to Many)

How does this look? Do I still need any secondary PKs anywhere? I have PCLNo and AppNo in all my tables - is this necessary?

Thanks for listening
Neil

 

[The_Doc_Man]

not confident that I've got it right

Neil, I rarely outright disagree with Pat, and it looks like she gave you good advice here. However, sometimes I have to remind folks - each of us must do what makes sense in the context of the application. We in the forum aren't there looking over your shoulder. We can offer suggestions, true, but in the end YOU are the one who must live with this layout. Pat can be just as wrong as any of us on any given situation. (She just has better odds of being right in the long run.)

Where I'm going with this is the issue of "confidence that you have it right." NONE of us ever know that we have it right until we TRY it. Pat's advice is just that - advice. YOU are doing the testing. If her advice just confuses you - back off from it. If you can't test her advice - don't use it right away.

I happen to think your layout makes sense, but I don't know your application either. Without that knowledge, all I can tell you is that it looks like a possible description of something that has a "parent, child, two grandchild" entity relationship. So here is some advice on how to gain confidence that you got it right.

Remember that Access is a business modeling tool among other things. (Gee, I thought it was just a database system...)

Your business consists of entities or objects that get represented in descriptive things called tables. The fields of the table are the descriptive parts, the attributes of the entities. Each row in the table represents one thing being described, one example of that class of business entity.

Things interact in a business. Those interactions must be somehow represented by data transformations within the tables. Queries and VBA code effect changes in tables corresponding to the business rules for these interactions.

If your table layout makes sense, it does so in the context of the business to which it applies. So if you can identify things in your business that are matched by your tables, you can gain that confidence of whether you have it right. If your queries and code perform actions that you can recognize as relating to your business processes, you can gain more of that confidence.

Conversely, if you are forced to go through all sorts of gyrations within Access to get it to track your business elements, you probably have something wrong. If you have lots of trouble seeing how table X relates to anything that you do, there is probably something wrong. Either you don't understand the business or the table-to-entity mapping isn't very good, one or the other - or even both, maybe.

If your business can be described simply in words, it should be described equally simply in Access. So the acid test for "got it right" is whether it is a good description of that part of your business that this application is trying to represent.

Never mind about what we think from out here. Your best evidence is sitting in or near your own office. It is whatever formal business procedure documents your company has published, whatever guidelines they have distributed, whatever they taught you via on-the-job training as "the way the business works." THERE is where you will find out if you got it right.

Hope that made sense to you. It was intended to motivate rather than admonish. Please take it that way.

 

[neilwebber]

Doc

I appreciate the time you've taken to reply and in no way do I see it as an admonishment. I'm a relativley new user of Access (in at the deep end) and this sort of advice about looking at the big picture is invaluable to me (and others like me?) as it doesn't seem to be covered in the few texts or training courses available to me (maybe I've got the wrong book).

I make a mistake in thinking that there are hard and fast rules governing this sort of thing and that people in the forum can supply 'off-the-peg' solutions. You point out that this isn't the case and I can't imagine why I thought this in the first place - thinking too hard about this has obviously affected the common sense circuits in my brain.

My table layout does make sense to me, constructed as it was from my knowledge of my business processes and I can equate the various relationships to the real world processes that the db is trying to represent. If I'm getting your point, I probably am on something like the right track and all thats left to do is to go away and try it and see if it works. I'll post back and let you (and other followers of this thread) know how I get on.

Again Doc, thanks for taking the time

Neil

 

[Len Boorman]

I would not disagree with anything that has been said before but just add a couple of comments from my experience.

a) When you have a design to try out think about what you feel will be the most difficult or complicated task. Do that one first. if that works then you are probably on the right track.

b) Normalise at least to remove repeating groups. I am at the moment organising the migration from two existing non normalised databases into a normalised database. The two I am migrating from are actually related in terms of data but there are no specified relational links. I can tell you it is painful although not especially difficult. It is amazing how many different ways there is to record a version number of a drawing when it is uncontrolled.

Do we all get it right first time ?. Do not know. I will own up to a couple of points.
I have never yet managed to specify all entities and attributes for an application. I always find there is something else needed somewhere.
I have laboured over the design and normalisation process of an application and then found that it would not work and have binned everything.

Quietly suspect that most have visited these situations.

There is always something new to learn so never despair that there are tbings you do not know. That is why there are forums such as this.

Above all keep smiling and test things first on a backup copy.

Len B