List:General Discussion« Previous MessageNext Message »
From:Andy B Date:October 28 2004 9:09pm
Subject:Re: column choices for certain data
View as plain text  

Thanks for the info/help here. I understand everything up to the part where 
it talks about referencing more than 1 table to each other and the foreign 
keys and all that stuff...

basically I got lost. Not because you explained it wrong but because I 
haven't got the foggiest clue what keys/table linking is or how it works...

Is it possible we can start with a simpler example with linking/keys/foreign 
keys and stuff so maybe I can follow it easier?? And of course if not valid 
for the list maybe we can take further talking to private email?? (sorry I'm 
very new at this stuff)...

I want to try and take this 1 step at a time until I get it...

let me know how I/we should go on from here. (the other side note) is I 
eventually have to work this db system into a php driven application (not 
like it matters on this list but...)

anyways let me know how to continue with the matter...

tnx for the help...

----- Original Message ----- 
From: "Rhino" <rhino1@stripped>
To: "Andy B" <aborka1@stripped>
Sent: Thursday, October 28, 2004 4:00 PM
Subject: Re: column choices for certain data

> ----- Original Message ----- 
> From: "Andy B" <aborka1@stripped>
> To: <mysql@stripped>
> 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

column choices for certain dataAndy B28 Oct
  • Re: column choices for certain dataAndy B28 Oct
  • Re: column choices for certain dataRhino29 Oct
  • Re: column choices for certain dataGleb Paharenko29 Oct