From: Warren Young
Date: September 10 2007 3:25pm
Subject: Re: SQLS
List-Archive: http://lists.mysql.com/plusplus/6994
Message-Id: <46E561D2.3000507@etr-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mandy Llanes wrote:
> 
> Ex. sql_create_#(NAME, COMPCOUNT, SETCOUNT, TYPE1, ITEM1, ... TYPE#, ITEM#)

This is a classic database design error.  What you have here is a 
one-to-many relationship, so you need two tables, not one:

	Table 1			Table 2
	-------                 -------
	id       >----------<<	id
	name			item
	compcount		type
	setcount

All of the "item, type" pairs are in Table 2, tied to a particular 
record back in Table 1 by some key field.  I showed it using a dedicated 
"id" field as the key, but this isn't necessarily required.  If the 
"name" column's values are unique in Table 1, you could tie the two 
tables together with that field instead.

The primary advantage of this design is that you don't have to keep 
adding columns each time you change the maximum number of "item, type" 
pairs.  There's no limit with this scheme.

Any time you have multiple columns with the same name except for an 
index value, it is almost always some variant on this design error.

The fact that this new design lets you work within the 25-column default 
limit for SSQLS is almost a bonus.  I'd recommend you do it this way 
even if the SSQLS default was sufficient.