Oops, I meant to copy the list on this reply too. Rhino
----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]> To: "Andy B" <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 4:00 PM Subject: Re: column choices for certain data > > ----- Original Message ----- > From: "Andy B" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, October 28, 2004 1:15 PM > Subject: column choices for certain data > > > > Hi... > > I have a db that I'm writing. It's for a business directory and one of the > > fields/columns in the table needs to have a list of business types in it > > (i.e. retail, auto, computer and so on). Since there may be more than one > > category that a business fits under I was wondering if "SET" is the best > > choice for that?? > > > I wouldn't use SET if I were you. > > I have never used the 'SET' column type in MySQL and had to look it up in > the manual to see what it did. However, I've worked with relational > databases for 20+ years (mostly DB2) and it doesn't have a 'SET' column type > in its repertoire; I've gotten used to doing things without 'SET' so maybe > I'm just being stodgy ;-) > > The chief advantage of 'SET', as far as I can tell from the manual, is that > it lets you control the specific values which can be in a column without > having to write application lookups to verify that the value you are > supplying is one that is valid for the 'SET' column. Therefore, if you had > only 3 business types, sole proprietorship, partnership, and corporation, > you could put those 3 values in the set and be sure that those are the only > 3 values that would ever be allowed in the column. That's fine as far as it > goes and is a very useful thing. > > However, on the negative side, there is a fixed maximum of 64 values in the > set. While that may be sufficient for your immediate needs, I don't think > you can be certain that it will be sufficient for your long term needs. For > example, if this is an eclectic business that combines a lot of lines of > business, you may find that it sells groceries, operates a dry cleaner, > contains a movie theatre, and umpteen other things all under the same > business name. You may find that 64 values isn't enough once you start > making the set include all the different functions of the business. > > The second negative is that I don't think 'SET' is a datatype found in most > other databases. Therefore, if you eventually port this table over to > another database, you may have to rework the design somewhat to get the same > effect, which could be a pain. > > The third negative is that putting multiple values in a single column of a > single row violates Codd's Rules, which are the foundation of all relational > databases. Codd is probably rolling in his grave at the mere thought of > doing this ;-) > > Therefore, let me suggest this, which should give you the same benefits > without the 64 value limitation while being portable to other databases: > store the business type in a separate table, even if there is only one > possible value for business type for most rows in your directory. > > For example, create one table to hold the basic information about your > business: > > create table businesses > (registration_number int not null, > business_name char(50) not null, > business_location char(100) not null, > [etc.] > primary key(registration_number)); > > Sample Contents: > registration_number business_name business_location > 1 Smitty's 123 Main Street > 2 Bob's 456 Park Street > > create table business_types > (registration_number, > business_type char(20) not null, > primary key(registration_number,business_types) > foreign key(business_type) references > business_types_lookup(business_type))TYPE=InnoDB; > > Sample Contents: > registration_number business_type > 1 pool hall > 1 dry cleaner > 2 restaurant > > create table business_types_lookup > (business_type char(20) not null, > business_type_description char(200) not null, > primary key(business_type)); > > Sample Contents: > business_type business_type_description > pool hall gambling establishment or other den of iniquity > restaurant eating establishment that can serve alcohol > > Do you see how this works? > > Every time you add a new business to your database, you add one row to the > Businesses table, assigning a registration number to the business and > recording company name, location, etc. Then, for each of the possible > business types that apply to that business, you add a row to the > Business_Types table. For example, Smitty's is both a pool hall and a dry > cleaner so you add one row for each business type, linking it back to the > Businesses table via the registration number that uniquely identifies one > business. Any given business can have as many business types as you like, > not just a maximum of 64. > > The third table is not strictly necessary but it is generally a good idea; > it is a lookup table that makes sure that only valid business types get > chosen in the Business_Types table. You create one row in > Business_Types_Lookup for each type of business that you think is valid for > your purposes. You can have as many business_types as you like in the > Business_Types_Lookup table, not just a maximum of 64. The 'foreign key' > designation on the business_type column of the Business_Types table ensures > that ONLY values from the lookup table are acceptable in Business_Types; if > you don't have 'shoe shine stand' in the lookups table, you can't use it in > the Business_Types table either. (Since foreign keys are only enforced in > InnoDB tables, you have to specify Type=InnoDB when you define the > Business_Types table.) > > Is this clear? If not, let me know and I'll try to clarify it for you. > > Rhino > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
