David,

> Oh no, no, no.  Get rid of most of those string fields.  Size, Group, and
> both colors should all be integers referencing other tables.

Why? Normalization have nothing to do with column types.
Using one column to store 2 values (colors), as Roy sugested, was the only
evident normalization issue I saw, and perhaps also the fact that  Kerry is
using a single table to Products and production information, but not knowing
the details of how the business works and without defining the keys in the
relation it's hard to say if it's normalized or how can it be normalized.

--
Fernando Dias
[Team Elevate]

Fernando

>Using one column to store 2 values (colors), as Roy sugested,

That one I might accept, but as you know I qualified it depending on the ultimate usage. Whilst I was in management consultancy I saw a vast amount of money wasted in (mainly government) projects first normalising things, then building a system around them and then finding it didn't work, was slow, cost more etc.

I tend to look at things highly pragmatically. I know the theory and I'll apply where suitable but one size does not fit all.

Roy Lambert

David Puett" <dpuett@gmail.com> wrote in message
news:C65E9743.36ECD%dpuett@gmail.com...

> Oh no, no, no.  Get rid of most of those string fields.  Size, Group, and
> both colors should all be integers referencing other tables.
>
> Here are a couple sources about data normalization

I don't think that is really normalization, more like a foreign key
constraint (not supported at a db level in dbisam).

I think it is a good idea to have size, group and colours referencing
another table/list to ensure data consistency, but given the likely size of
the database agree with Roy that using the caption as the key (a string)
simplifies things for querying reporting.

John

John

>I think it is a good idea to have size, group and colours referencing
>another table/list to ensure data consistency, but given the likely size of
>the database agree with Roy that using the caption as the key (a string)
>simplifies things for querying reporting.

If you have a single data entry point / program you can ensure consistency with a simple hardcoded picklist. Not as flexible as having another table but with the number of items involved if you need to add another a quick recompile and you're away. Sorry I'm a heretic

Roy Lambert

Roy

> If you have a single data entry point / program you can ensure consistency
with a simple hardcoded picklist. Not as flexible as having another table
but with the number of items involved if you need to add another a quick
recompile and you're away. Sorry I'm a heretic

If you are programming for yourself/ one client sure.  Way back in the day I
wrote an app with a fixed rate of VAT at 15%.  A couple of hundred users/no
broadband ..... if they could have just put up wuth it for 20 years

John

David,

<< Oh no, no, no.  Get rid of most of those string fields.  Size, Group, and
both colors should all be integers referencing other tables. >>

I wouldn't go that far - I would make them other tables with foreign keys
pointing to them, but I would just use the color name as the primary key of
the lookup table.  Much easier to manage and easier to deal with in the
tables using such values.

--
Tim Young
Elevate Software
www.elevatesoft.com

John

>If you are programming for yourself/ one client sure. Way back in the day I
>wrote an app with a fixed rate of VAT at 15%. A couple of hundred users/no
>broadband ..... if they could have just put up wuth it for 20 years

So you were just a bit ahead of your time - we do have a VAT rate of 15%

More seriously, my original take on the post was its an internal app for a fairly unsophisticated and small operation. Keep it as simple as possible and when the company reaches the point of needing something more sophisticated there are masses of MRP/ERP packages out there starting at a few hundred pounds to monsters like SAP.

Roy Lambert

ps is it really 20 years since it went to 17.5%

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:6F70456E-0824-45A6-A313-A4B2CF81DE1E@news.elevatesoft.com...
> David,
>
> << Oh no, no, no.  Get rid of most of those string fields.  Size, Group,
> and both colors should all be integers referencing other tables. >>
>
> I wouldn't go that far - I would make them other tables with foreign keys
> pointing to them, but I would just use the color name as the primary key
> of the lookup table.  Much easier to manage and easier to deal with in the
> tables using such values.
>

Any description can be changed. Future uses of software many times are
unpredictable. Someone wants to change "Red" to "Pink" because it better
reflects the color of this product, or to "Rojo" because he wants to use the
software in Spanish, and all of a sudden the foreign keys don't work any
more. I agree with the use of integers. Of course, there is no need for the
end user to know anything about those integers.

Robert

Roy

> ps is it really 20 years since it went to 17.5%

Well 18 to be accurate - it went up on 1/4/1991.

John

Robert,

<< Any description can be changed. >>

It's not the description of the color, it's the color code.

<< Future uses of software many times are unpredictable. Someone wants to
change "Red" to "Pink" because it better reflects the color of this product,
or to "Rojo" because he wants to use the software in Spanish, and all of a
sudden the foreign keys don't work any more. >>

No, not correct.  They can't just change them without violating the FK
constraints.  Such a change has to be done carefully in a transaction, which
is how it should be done.  And I would say that your examples are not
particularly good - Pink would be added as a new color, and the tables
updated accordingly, and in the case of a Spanish conversion, such a
conversion would be done in the form of translating the entire database to a
new language, not a single change.

--
Tim Young
Elevate Software
www.elevatesoft.com