[thelist] function_id

[rudy]

hi russel

> INSERT INTO categories VALUES (1,'Sportsman','null');
> INSERT INTO categories VALUES (2,'Hunter','1');
> INSERT INTO categories VALUES (3,'FoxHunters','2');
> INSERT INTO categories VALUES (4,'BirdHunters','2');
> INSERT INTO categories VALUES (5,'WabbitHunters','2');
> INSERT INTO categories VALUES (6,'Owner','3');
> INSERT INTO categories VALUES (7,'Breeder','');

no, there are still problems here

the structure is okay, but the data is bad

for one thing, cat_id and parent_cat_id are both numeric,
so you cannot insert those strings into parent_cat_id

the string 'null' is not the same as the reserved word null

you cannot use a zero-length string for a null

and finally, the examples seem wrong, and perhaps it is
just because they are contrived, but it could also be that
the foreign key relationships are not well understood

indented, these relationships look like this --

Sportsman
--Hunter
----FoxHunters
------Owner
----BirdHunters
----WabbitHunters
Breeder

i don't understand why Owner is a subcategory of FoxHunters


> CREATE TABLE persons(
>   personid bigint(12) DEFAULT '0' NOT NULL auto_increment,
>   name varchar(20),
>   catid integer (3)null,
>   parentcatid  integer (3)  null,
>   foreign key (personcat),
>   PRIMARY KEY (sportsmenID)

no, you would not relate a person with catid *and* parentcatid

foreign key personcat and primary key sportsmenid do not exist
in the table -- perhaps some hasty and unedited cuttin & pastin?

i'm sorry to say this, but you have to be 100% precise in order to
achieve results with sql

how many people are you going to record in this table?
i'll bet BIGINT is several orders of magnitude too big


> INSERT INTO persons VALUES (1,'Fred','4');
> INSERT INTO persons VALUES (2,'Walker','6');

number of values does not match number of columns


> *? Walker is also a Sportsman, Breeder, Owner,Foxhunter, WabbitHunter
> *How is this coded?

you are describing a many-to-many relationship

each category can contain more than one person, and each person
can belong to more than one category

therefore you have to move the person-cat relationship into a separate, new
table

  create table categories
   ( catid  smallint  not null  primary key
   , catname  varchar(100)  not null
   , parentcat  smallint  null
   , foreign key ( parentcat ) references categories ( catid )
   );
  create table persons
   ( personid  smallint  not null  primary key
   , personname  varchar(100)  not null
   );
  create table person_category
   ( personid  smallint  not null
   , catid  smallint  not null
   , primary key ( personid, catid )
   , foreign key ( personid ) references persons ( personid )
   , foreign key ( catid ) references categories ( catid )
   );

rudy