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
>> I have a db that I'm writing. It's for a business directory and one of
>> 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
> 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
> 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
> 3 values that would ever be allowed in the column. That's fine as far as
> goes and is a very useful thing.
> However, on the negative side, there is a fixed maximum of 64 values in
> 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.
> 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
> other databases. Therefore, if you eventually port this table over to
> another database, you may have to rework the design somewhat to get the
> 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
> 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
> create table businesses
> (registration_number int not null,
> business_name char(50) not null,
> business_location char(100) not null,
> 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
> business_type char(20) not null,
> primary key(registration_number,business_types)
> foreign key(business_type) references
> 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
> 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
> that ONLY values from the lookup table are acceptable in Business_Types;
> you don't have 'shoe shine stand' in the lookups table, you can't use it
> 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.