File Length - Compact

[VivDenham]

Hi

I've re-read both the above (and many other items found by Googling). However, as far as I understand it, the main requirements of Normalization are a) to only record 1 item in a table, b) not to duplicate data, and c) not to have redundant data not linked to that ID.

In my database, T-01 - Style - Tops - Arm records all the different types of Arms that a Jumper could have. This table contains the ID, Arm name, a picture, and any notes relating to that Arm. This is the only place this information is recorded. And none of the data is redundant, it is all pertinent to the ID.

Then, when actually deciding on the Style of Jumper I am going to knit, I go to the table T-01 - Style - Tops, and record an ID and Name for that Style, and select the Arm type that I want (i.e. whether it will be a long arm or short).

So as I understand it, the Arm is only recorded once, in the Arm table, and the Jumper Style is only recorded once, in the Style - Tops table. These 2 are then linked with Relationships.

Am I misunderstandin g something?

Many thanks for trying to point me in the right direction.

Viv

[dsatino]

Very sorry for the delay, I've just been hoping to put together an example for, but I'm just really busy so for the sake of not ignoring you...

Your general understanding of keys and non-redundancy is basically correct, but your implementation is where the issue is.

Part of the normalization concept is to store similar data together in a single table (usually). In your db you have separate tables for cuffs, collars, necks, etc. These may be physically different, but from a data standpoint they are the same in that they are attributes of a top and thus should be stored together. I promise I'll show you an example when I get the time to build one out.

[VivDenham]

Hi dsatino

Thanks for all your efforts - the cogs in my brain are whizzing round just now.

I can see the logic of having a table for attributes of Tops (i.e. component parts of a jumper) and that leads me to think that I have a stage of the logic (and hence, a table) missing - perhaps I should have a table called Tops - Component Parts, and these component parts would be things like Welt Type, Waist Type, Armhole Type, Neck Type, Cuff Type, Arm Type, Sleeve Head Type, etc.

But to my thinking, that just means 1 extra table. How would I then get all the different types of cuff, armholes, etc into that 1 table?

And further down the line, even if I could get all these components with their multitudes of cuffs types, armhole types, etc all into the one table, how would I then run all the cascading queries, without getting the error Query Too Complex. I would still have to use MTQs, wouldn't I? And that would still lead to the bloat?

I can't wait to see what you come up with. But once again, thanks for all the time and effort you are spending on me. It is so very much appreciated.

Viv

[dsatino]

Ok, here's the quick example. I'm not saying it's perfectly right since I don't enough about your products, but I hope it gives you the general idea of what I'm conveying. There's only 5 tables and although I only added a small bit of the data, you can add all of the products into them.

You'd need other tables for the stitching info, but the same concept would apply.

As for the calculations, I honestly haven't even looked at that piece yet, but I assure you that it can be done under this structure and without the MTQ's.

[VivDenham]

Hi dsatino

Thanks for the example db - I shall spend the weekend going over it and sorting out how I can use the format myself.

Thanks again.
Viv

[VivDenham]

Hi dsatino

Before I go too far down the Normalisation road, I wonder if you would have a quick look at what I've done so far. My brain works in such a way that I have to have tables, queries, etc listed in the correct order that the process takes, so I am just checking that I am understanding things before I do too much work which may be incorrect.

Many thanks, once again for all your help.

Viv

[dsatino]

The naming of the tables in mostly unimportant. It's definitely good practice to give them meaningful names, but not necessary. I think if the names you've given the tables help you then keep the convention you've been using, but I would definitely suggest a slight modification in which you get rid of the spaces and hyphens and use underscores instead. For example:

T-01 - 04 - Products - Product Components would be something like
T_01_04_Product s_ProductCompon ents

This doesn't have anything to do with normalization, but it's just easier when referencing the tables in code.


As for the normalization part, I think you're getting the table structure part, but not quite the data redundancy part.

For example, in your "T-01 - 05 - Product Component Types" table, you are still using the Product and Component names in the component type name. There is no need for this because the tables are related through the foreign keys and hence you don't need to store the foreign table descriptions in this table.

The main reason why you don't want to do it this way because it defeats your ability to make changes in a single location. Let's say, for example, that you wanted to change your product "Scarves" to "Neckwear" instead. Under your current method, you would have to make this change in every instance in your database which is numerous tables and could be thousands of rows. This basically defeats the purpose of normalization.

If you look back at the DB I posted you'll see a single query. This query essentially constructs all the names you have, at any level you need to see it. Now to see the concept I'm explaining, close the query, open the "ProductTyp e" table, change "Tops" to "Shirts", close the table, and then re-run the query.


This is basically the concept of normalization in action.

[VivDenham]

Thanks, dsatino. I realise I'm doing things very much in a "Belt and Braces" fashion - just can't trust myself to rely on the Tables to work correctly for my purposes.

So, it's back to the drawing board. Thanks again.

Viv