Simple MYSQL Normalization question - input requested

**ByteCoder** · 19-02-2009

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?

**Katty** · 19-02-2009

Eventually you'll regret it. Mainly because it would be coded in such a way that would sit that client and that client alone. You'll have more clients in the future and the time on that database + Model + Controller will be wasted time.

If you can design it perfect - because no one can - design it the best you can/know.

Code:

TABLE BRANDS { 
int primary KEY 
string name } 

TABLE COSTUMERS { 
int cost_id primary KEY 
string cost_name } 

TABLE COSTUMERS_TO_BRAND { 
int KEY costumers.id 
int KEY brands.id }

And just join them as you may.