Re: Simple MYSQL Normalization question - input requested.
- From: Jerry Stuckle <jstucklex@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2009 13:16:44 -0500
slangtruth@xxxxxxxxx wrote:
Hi-
I'm writing a simple web-based database application for a local
company, and would like to solicit some advice from the more
experienced programmers here on a particular issue. The company issues
a mail order catalog once a month of items for sale, each catalog has
200-300 unique items. They enter items as they come in and build the
catalog at the end of the month, so over the course of a year they'll
be entering at most 5000 items (probably fewer), and since each item
is unique they pull and archive the completed items once the catalog
is complete at the end of the month. So even though there is some
carryover, it's likely the main database won't grow over 1000 records
at any particular time, and again will probably usually be fewer.
They also have a list of broad categories to assign to items at entry
time - call them Ford, Chevy, Dodge, Subaru, Mercedes, etc. They have
a customer mailing list (not yet linked) where customers have
indicated interest in a particular brand, and going forward they want
to link the two so that if there are several Ford items in the catalog
they want to make sure that all their Ford customers get one.
I initially gave them a single field to categorize their items. The
categories are in a separate table with their own primary key - let's
say Ford is #1, so in the main item record there will be a 1 in the
Category field. Now they're saying that some items may in fact appeal
to multiple interest groups, so they may want to have more than one
category attached to an item. Without messing up my carefully planned
input form I think I can stick in up to four select boxes for
categories, and they've said that would be plenty, and that they'd
probably never even use all four.
So my question is, aside from the fact that it goes against
theoretical principles, is there any =practical= reason why I should
break these out into a separate table and not just add Category2,
Category3 and Category4 fields to the item database? The only SELECT
which will ever run on this field (and it will actually be running on
the 200-300 record extracted table, not the "big" table) would change
from the simple
select from items where category = 1
to the rather more unwieldy
select from items where category = 1 or category2 = 1 or category3 = 1
or category4 = 1
but that wasn't all that hard to type, and it will be buried in code
where they'll never see it. I can't see with this size table that
there's ever going to be a real performance advantage one way or the
other, and all the surrounding code to save, extract and edit records
will be simpler if I can keep it to one table rather than two. What do
you think? Is there something I'm missing?
And your PHP question is?
This is a MySQL question, and should be asked in a MySQL newsgroup - such as comp.databases.mysql. That's where the MySQL gurus hang out, and you'll get much better answers.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================
.
- References:
- Simple MYSQL Normalization question - input requested.
- From: slangtruth
- Simple MYSQL Normalization question - input requested.
- Prev by Date: Re: Query string before parsing
- Next by Date: Re: Query string before parsing
- Previous by thread: Re: Simple MYSQL Normalization question - input requested.
- Next by thread: Re: Simple MYSQL Normalization question - input requested.
- Index(es):
Relevant Pages
|
