Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 19-02-2009
Member
 
Join Date: Oct 2008
Posts: 27
Simple MYSQL Normalization question - input requested

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?
Reply With Quote
  #2  
Old 19-02-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Simple MYSQL Normalization question - input requested

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Simple MYSQL Normalization question - input requested"
Thread Thread Starter Forum Replies Last Post
Driver question -PCI Simple Communications Controller Bailee Windows Software 4 21-08-2010 06:30 AM
Simple Workgroup question. technonot5000 Networking & Security 3 02-11-2009 02:40 PM
Input/Suggestions requested about cyber/game cafe mansoons25 Hardware Peripherals 3 10-04-2009 10:08 AM
Very simple request in MYSQL but getting no output CodGuru Software Development 4 27-03-2009 11:51 PM
HTML- simple css question Kerwin Software Development 4 25-10-2008 04:37 PM


All times are GMT +5.5. The time now is 01:09 PM.