Hi...
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" <[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]
