List:MySQL++« Previous MessageNext Message »
From:Warren Young Date:September 10 2007 3:25pm
Subject:Re: SQLS
View as plain text  
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

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.
SQLSMandy Llanes10 Sep
  • Re: SQLSGraham Reitz10 Sep
  • Re: SQLSWarren Young10 Sep
  • RE: SQLSJim Wallace10 Sep
    • Connection object timeout question...Graham Reitz10 Sep
      • Re: Connection object timeout question...Warren Young10 Sep
        • Re: Connection object timeout question...Graham Reitz10 Sep