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
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.