MYSQL :: Table Of Users Having Many To Many Relationship With Itself?

Table Relationship
i'm currently modelling our new portfolio: our projects will be stored in a "projects" table.We would like to be able to link projects to one or more other projects.I figure the logical way is to have a table "project_associations" containing all the relationships.
My question is: is this the right way to do?
It means that table will have two foreign_keys pointing to the same "project" table:

CREATE TABLE `project_associations` (
`association_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`this_project` int(11) UNSIGNED NOT NULL DEFAULT '0',
`association_type` set('crosslink','edition','release','project','mode')
DEFAULT 'crosslink',
`other_project` int(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(`association_id`)
)
ENGINE=MYISAM
ROW_FORMAT=default;

Separate Table For 1-1 Relationship?
I have a table holding invoices with all their data. Sometimes, but quite rarely, there will be a correcting invoice which will be stored in the same table but it will reference another invoice. I'm wondering if it's worthwhile to create another table for linking the correcting invoices to others. One correcting invoice can reference maximum 1 other invoice. There are many columns in the table, but here the most important are:

CREATE TABLE invoices (
invoice_id mediumint(8) unsigned NOT NULL auto_increment,
invoice_no varchar(40) default NULL,
correction_for mediumint(8) unsigned NOT NULL default Ɔ',
correction reason varchar(255) NOT NULL default '',
ivoice_type enum('normal','correcting','proforma') NOT NULL default 'normal',
PRIMARY KEY (invoice_id),
UNIQUE KEY invoice_no (invoice_no),
) ENGINE=InnoDB;

Now correction_for will hold invoice_id of the invoice being corrected or 0 if this invoice is not of correcting type. I'm wondering if it would be better to get rid of correction_for coumn at all and create another table for referencing the corrected invoices - what would be a better design?

MySQL Table Relationship
I used to work with MS Access and now I have to learn MySQL VERY FAST. I have read about 4 books and I don't understand how to create tables linked by relations. If I want to create for example an E-Shop do I need relations and if yes could anybody show me an example of relationship ? If I don't need relations between the tables how can I use the tables ?

Lookup Table For Many To Many Relationship
Hi all, right now i have three tables setup as such:

Table [doc]
doc_id
doc_name
project

Table [lookup_table]
doc_id
element_id

Table [categories]
category
sub_category
element_id

basically, categories has a table of keywords which are assigned to a single document from the doc table. More than one keyword can be assigned to a document. Someone can search for a document by specifying keywords, and mysql should return all documents that have the specified keywords assigned to the document. The lookup_table is used to keep track of which document has what keywords assigned to it. For example, I have a doc name "test" with doc_id = 1, and I have assigned 5 keywords to it, the keywords have element_id of 1, 2, 3, 4, and 5. So the lookup_table would be:

doc_id | element_id
....1..........1........
....1..........2........
....1..........3........
....1..........4........
....1..........5........

this way i can see that document 1 has keywords 1, 2, 3, 4, and 5 assigned to it. Now I have few hundred documents each having multiple keywords assigned to it. How would I get all the documents that have keywords 1 and 2 assigned to them? I have tried using JOIN and UNION:.....

Table Relationship Assistance
I am doing a website for a guy who sells campers. Each camper will one to several features, such as carpeting, toilet, shower, electicity, dining area, awning, etc., etc. When he enters a camper I will have a form with a list of checkboxes, each for a particular feature. Can some tell me how these would work in a relationship between two tables, one being campers, the other features? Woudl each feature be a column in the feature table, and the feature table would also have an id and a fk of camper id?

MySQL Table Relationship Problem
I have the following table:
tableA (
intUser1,
intUser2
);

The values in the table are (1,2), (2,3) and (3,1).
I would like to select intUserIds from table who are associated with 2.
So in my case I would get the result 1 and 3.

I used a query saying:
SELECT intUserId1, intUserId2 FROM tableA WHERE intUserId1 = 2 OR intUserId2 = 2.
The problem is that this query always returns two columns, one of them being value 2. How can I select only the columns associated with 2?

Best Way To Select Records WITHOUT Relationship In Another Table?
I have a stock table and a stock_category_r table which is a relationship table between stock items (products) and stock categories (M:M relationship)

I need to select stock that is uncategorised. I.e Items that have not been related to ANY categories.

I was using this query with a subquery:

SELECT s.ID, s.code, s.name
FROM `stock` s WHERE s.ID NOT IN
(SELECT stockID FROM stock_category_r)
Just wondering if this was the most effecient way to find stock records that have no corresponding relationship in stock_catgegory_r ?
Do sub queries effect performance much?

My tables for this app have very few rows, but I'm interested in the best-practice theory

The table structure:

CREATE TABLE `stock` (
`ID` smallint(5) unsigned NOT NULL auto_increment,
`code` varchar(16) NOT NULL default '',
`name` varchar(40) NOT NULL default '',
`description` mediumtext NOT NULL,
`image` varchar(35) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `stock_category_r` (
`stockID` smallint(5) unsigned NOT NULL default Ɔ',
`categoryID` tinyint(3) unsigned NOT NULL default Ɔ',
PRIMARY KEY (`stockID`,`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

How To Create A Join/relationship Table That Maps To Many Tables
Let's say I had the following table structure:

CREATE TABLE customer
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service3
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service
(
subscription_id INT UNSIGNED NOT NULL,
service_type TINYINT UNSIGNED NOT NULL,
service_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service_type, service_id)
) ENGINE = INNODB;

CREATE TABLE service_type
(
id TINYINT UNSIGNED NOT NULL,
service_table_name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO service_type (id, service_table_name) VALUES
(1, 'service1'),
(2, 'service2'),
(3, 'service3');
I need a way to map Customers to Subscriptions to Services.

Please keep in mind that one subscription record can be related to many services: even services of the same type.

The above structure would work, but I wouldn't be able to make it transactional. The problem lies with the subscription_2_service table's service_id column. That column doesn't actually reference any other table. It's conditional, meaning its value changes depending on what the service type column value is.

This makes it impossible to implement transactions because the service_id column references nothing concrete.

I considered another way of doing the above by sticking a subscription_id column in all three service tables. I don't like this approach because not all services will require a subscription. This would result in many NULL values and tight coupling between the subscription and service tables. It also seems sloppy/scattered.

I also considered a third way of creating subscription_2_service tables, such as:

CREATE TABLE subscription_2_service1
(
subscription_id INT UNSIGNED NOT NULL,
service1_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service1_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service2
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service3
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;
This third method above seems ridiculous.

The third approach does decouple the subscriptions and services from one another. Services aren't aware that they're part of a subscription and subscriptions aren't aware of what services have subscribed to them. This method still seems bloated and wrong though.

Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed.

[table] users
[field] userid

[table] user_details
[field] userid

Select userid from users where userid not in user details

Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it.

Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules.

I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more.

Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores:

TABLE: feedback

identities_id int(10) <- contains the user id which is stored in another table
feedback int(10) <- contains the feedback score for a specific date and time
date_recorded datetime <- date the feedback was captured
I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results):

mysql
SELECT user1.feedback AS user1feedback
, user2.feedback AS user2feedback
, UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded
FROM feedback as user1
LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded
WHERE user1.identities_id = 1
OR user2.identities_id = 2
AND user1.date_recorded > NOW() - INTERVAL 30 DAY
ORDER BY date_recorded ASC

This returns virtually the same data for both users which means I am not distinguishing the two properly.

Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table.

The tables are

pictures
user (Example FRED)
picture (Example pic.gif)

users
id (Example 5233)
user (Example FRED)

What I want to do is replace the user column in pictures with the correct id from the users table

So in the pictures table, it would no longer be FRED it would be 5233

Is there a query I can run to do this?

I'd really appreciate some feedback

Obtain Most Popular Cities From My Users Table
I've got a 'users' table:

USER_ID --- CITY_ID
1 ---- 32
2 ---- 20
3----- 32
4 ---- 32
5 ----- 17
....

I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?

Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:

Many-to-Many Relationship
I am new to normalizing and many-to-many relationships in MYSQL but I am trying to change my bad habbits of table structure.
I kind of guessed my way through my first problem and its all working now, but I cant seem to work out my second.

Basically I can list all the users, and list all their skills, using the many-to-many relationship. But I cant work out how to list only the users with a certain skill.

Any help is great, and a brief explaination of how it works would be excellent, then I wont have to pester you nice people again

Code im using is below, thanks again, Harro.

PHP

<?
if(isset($skills)) {
      $get_users = "/* STUCK HERE */";
      } else {
      $get_users = "SELECT * FROM users ORDER BY user_id DESC";
      }
      $do_get_users = mysql_query($get_users);
      while ($users = mysql_fetch_array($do_get_users, MYSQL_ASSOC)) {
      echo "<div id='member'>
       <div id='member_pic'>
        <img class='member' src='/images/members/".$users['user_name'].".jpg' />
       </div>
       <div id='member_info'>
        <strong>Name:</strong> ".$users['first_name']." ".$users['last_name']."<br />
        <strong>Age:</strong> 22<br />
        <strong>City:</strong> <a href='#' class='city'>Liverpool</a><br />
        <strong>Skills:</strong> ";
        $get_skills = "SELECT users_skills.user_id, users_skills.skill_id, skills.skill
                       FROM users_skills
                       LEFT JOIN skills ON (skills.skill_id = users_skills.skill_id)
                       WHERE (users_skills.user_id ='".$users['user_id']."')";
        $do_get_skills = mysql_query($get_skills);
        while ($skills = mysql_fetch_array($do_get_skills, MYSQL_ASSOC)) {
        echo "<a href='#'>".$skills['skill']."</a> ";
        }
        echo "</div>
         <div class='clear'>
         </div>
        </div>";
      }
?>

Using A Many-to-many Relationship
I'm currently putting together a database using MySQL for a friend of mine, cataloguing some of his books. At the moment I've come to a stumbling block in the database design. The majority of the books are written by one author but there are some that are written by two or more. I'm quite a stickler for good database design/normalisation so ideally I'd like to have a books table, a writes/is written table and an authors table. ie. a many-to-many relationship with writes/is written as the joining table.

The problem or question I have is this: What then happens for inserting and updating information about a book and adding authors to it. What sort of SQL statements will I need to make sure that the author info is connected to the book each time.

Is it like this:

SELECT b.title, a.name
FROM books as b, authors as a
WHERE b.id = writes.book_id
AND writes.author_id = a.id

and what would the INSERT look like?

Sql Relationship
i have two tables, one for articles and one for comments. articles [ aid "key", subject, article ] and comments [ cid "key", caid "foreign", reply ].

i have a page wher i list my articles with a comment form on the bottom. i dont know how i can make the comment form get the [aid] from the other table.

Dim rsUsers

set rsUsers = Server.CreateObject("ADODB.Recordset")

rsUsers.Open "comments", conn, 3 , 3
rsUsers.AddNew
rsUsers("cid") = Request.Form("cid")
rsUsers("caid") = <---- what should i put here ???
rsUsers("reply") = Request.Form("reply")
rsUsers.Update
rsUsers.Close

%>

1 To Many Relationship, How To?
I am trying to create a 1 to many relationship between two tables. How do I go about doing this, preferrably using MySQL Query Browser. IS this done through a foreign Key?

One To MANY Relationship
I am setting up a database with some 20 tables . I wish to setup a one to MANY relationship so that all of these tables are inserted into a "master table". From there I would like to be able to make changes and delete certain data on the 'bottom'. But I don't have the slightest clue as to how to make it update the 'chain'. If change something on one of the tables, I want it to make it update the "master table" and any other tables 'linking' to that data. I couldn't find anything when I went searching on the forum (although I know its there),

Relationship
where and how can I find a full view of all the tables in one database. I remember seeing one in Microsoft SQL, it show all the tables with all the column names. Each table has a relationship link with other tables. Showing which is the primary key and etc.
Please let me know how and where can I create it. And does mySQL has something similar to it?

Inserting Into A Many-to-many Relationship
I'm having a lot of trouble figuring out how to insert data into my setup. I layed out the relevant fields below..

imagespic_idtags_xrefpic_idtag_idtagstag_idtag - UNIQUE
how can I insert all unique tags, at the same time inserting into xref? I was thinking that I would first have to insert ignore into tags.. but then the xref table would have to know the successfully inserted tag_id's PLUS the tag_ids that are already in the tags table that are relevant to pic_id.. in order to do it's own insert.

I hope i'm making sense, if not, please let me know i'll try to explain this better. Any help would be greatly appreciated.

Relationship Tables
I'm currently (slowly) developing a site in my free time to learn new concepts etc. more of a learning experience than anything serious... and I've hit a brick wall when it comes to relationships between tables in MySQL

I have currently run into and solved the following problem, but I fear my solution may not have been the best way to design the database, perhaps you DB Guru's could help out...

I have a User table (which holds all the usual user table type info) and I have a Website table (which holds a website title, and URL) each with their respective Primary keys (user_id and website_id). A user can be related to many websites and the website can be related to many users, so a many to many relationship. To connect these two tables I create a lookup table (user2website) which contains the two respective primary keys (user_id and website_id). Here is where I run into a problem.

The user can have 3 types of websites:

i. the one they currently own
ii. a website they have managed
iii. a website they have visited

The user can only have 1 of [the one they currently own] and any number of the other types of websites [a website they have managed] and [a website they have visited]

So far my solution is... since the User can only have 1 main website.. [the one they currently own], then I should implement the many-to-one design (a website can have any number of "owners"), and create a new column in the User table called website_id.

Then I create a lookup table that holds user_id, website_id and relation_id (Relation table holding the type of relation [a website they have managed] and [a website they have visited])

I don't know if my explanation was good enough, but for those who managed to understand whats going on, is this a good solution? Or is there another way to implement this?

Relationship In MySQL
I am trying to build the following basic M:N relationship in MySql: An Event can be managed by many Organizers, and an Organizer can manage many events. So I built the following tables:

event
-----
eventID (pk, auto-increment)
eventName

event_organizer
---------------
event_organizerID (pk, auto-increment)
eventID (fk to event.eventID)
organizerID (fk to organizer.organizerID)

organizer
---------
organizerID (pk, auto-increment)
organizerName

Where the event_organizer table brigdes Event and Organizer. In this table, eventID and organizerID are foreign keys to the same attributes in the event and organizer tables, respectively.

I built a nice form in PHP so the users can query the databse and add their own events. When adding new events, users specify the name of the event and the organizer they want to assign to it.

What would be the SQL (INSERT?) syntax to add "Joe's Birthday", organized by "Mike" into the db? I am having a hard time auto-updating the bridge table, so that every time an event is added, and an organizer (from the Organizer table) is selected, event_organizer automatically bridges the two.

I have built many databases in Access, and have got used to the auto-bridge function it offers. The recent transition to SQL, however, has challanged me a bit - this is one of the issues I am facing.

DB Relationship Questions
I'm trying to set a Primary Key / Foreign Key relation using phpMyAdmin.

1. I assume that the Foreign Key is a Primary Key in its own table, and that really the relationship is between to Primary Keys, one that behaves as a Foreign Key. Is that correct?

2. It seems that if I select a table, I can then select 'Relation view'. That's where I seem to define this relation. Do I define it from the table that has the primary key, or from the table that has the foreign key? Code:

Relationship Question
I have three tables like this:

tbl_BusinessType
Business_Type_ID | Business_Type

tbl_Business_Sub_Type
Business_Sub_Type_ID | Business_Sub_Type

tbl_Category
Category_ID | Category

I want to relate these for the purposes of making a triple combo drop down so, is this the way to make the relationship or, is there a better way.

a table like this:

m2m_Types_SubTypes_Category
Relationship_ID | Type_ID | Sub-Type_ID | Category_ID

if so, what should the query be like?

Relationship Between 2 Tables
How can i create a relationship between 2 tables?

Relationship (one To Several) Between Mysql
How can I stablish the relationship between MySQL tables (one to several)?

Entity Relationship
Is there a MySQL command with which I could view all the entity relationships between all my tables in the database? I'm looking for a way to create an ER diagram without going into each and every table.

Relationship Databases
Does anyone have any good resources or know of any good books to learn about relationship databases?

And is this how they work: Table one has: Author info, Table Two has: Book info. I can make a relationship database that will related the author to all the books he wrote based on a "foreign ID" which will be a primary key in the other table?

Resolving M:N Relationship
I want to create multiple groups, and multiple users in my db. As I'm sure you can imagine, a user can be a member of multiple groups, and likewise, a group can have multiple members. I've never had to resolve these kinds of relationships before, as I'm pretty new to MySQL - and it's a while since I done any normalization. I know I have to create a common field, or something like that, but can somebody help me out on what I need to do. Should I create a table with group and user, and each time a user is ina group, add a record with the group and username. Then I'd still need to have another table with just the list of groups though, so I'm guessing there's a better way of doing this?

Relationship Failure
The following code is executing succesfully! there is nothing on the courses table so why am I able to insert into the labCourses table give the foreign key constraint

create table if not exists courses(
courseNo varchar(10),
subjectCode varchar(10),
constraint myCnsCourses primary key (courseNo,subjectCode)
);

create table if not exists labCourses(
labCode varchar(10),
courseNo varchar(10),
subjectCode varchar(10),
constraint mycnst foreign key(courseNo,subjectCode)
references courses (courseNo,subjectCode)
);
insert into labCourses (courseNo)
values ('hi');

Merging Data From A Many-to-many Relationship
I have three tables in my database:
books {book_id*, title}
authors {author_id*, name}
book_author {book_id*, author_id*}

The book_author table joins books and authors in a many-to-many realtionship. I join the tables with the following MySQL statement:

SELECT book.title, author.name
FROM book, book_author, author
WHERE book.book_id = book_author.book_id
AND book_author.author_id = author.author_id;
The problem I have is that if a book has more than one author, the title appears multiple times for each instance of author as a seperate row in the table. eg.

PHP & MySQL Web Development Luke Welling
PHP & MySQL Web Development Laura Thompson
Is there anyway to have the single title with multiple authors? When I

GROUP BY book.title
only one author is shown. eg.

PHP & MySQL Web Development Luke Welling
What I would prefer is something like this:

PHP & MySQL Web Development Luke Welling, Laura Thompson

No Relationship Between Tables In MySQL
I'm using phpMyAdmin and MySQL 4.1 and since I'm new to MySQL I'm creating a test database using SQL statements from the "MySQL Tutorial" book by Welling and Thomson. I typed out the SQL statement into the SQL view as follows... Code:

Complex Queries On Many-to-Many Relationship
I'm building a system that involves performing fairly complex queries against a set of member data. The member data consists of answers to a series of questions, each question is multiple choice, with the possibility of the user selecting multiple answers to a question. As such, Answers to Member accounts is a many-to-many relationship. My table structure currently looks like this: (with extra, un-related fields removed) Code:

Relationship Diagram Generator?
just wondering if I could mysqldump a schema from a exsiting database, is there any relationship generator that can help me to get a diagram of how the tables are related?

Entity Relationship Diagram
is there a tool available, which generats an
entity relationship diagram by a given, existing
database?

Relationship Of MySQL And MaxDB
Is MaxDB ultimately a "souped up" MySQL?
Does anyone know what the plan is? i.e., Is the company going to
converge these? Continue to market separate products?
Also, how divergent is the code between MySQL and MaxDB?

A Basic Mysql Relationship Question
cREATE TABLE Topic (
Topic_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Topic_name TEXT,
Topic_info TEXT
);

CREATE TABLE Img (
Img_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Img_name TEXT,
Img_author TEXT
);

What i want to do is include the Img_ID in the Topic table(as a foreign key??) so i can refer to it through that table.

do i do it like this:

CREATE TABLE Topic (
Topic_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Topic_name TEXT,
Topic_info TEXT,
Img_Id INT NOT NULL AUTO_INCREMENT FOREIGN KEY,
);

Establishing A Relationship In A Family Tree
I have a table which describes every person in a family and another table that has 2 foreign keys from the main person table and an id of a relationship b/w them. i need a stored procedure to insert data into this table ,that can extract the primary id from the main table twice and the relation id from another type table. am stuck and need some direction regarding this.

How To Detect Circular Reference/relationship With SQL
What's the common SQL code used to detect circular reference/relationship.

E.g.

This simple category can add other other category as
its children or set other category instance as its
parent.if there is a select clause that can detect if descendants of a category instance
has the category instance's parent as its children.

CREATE TABLE category (
category_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
parent_category_id int(10) NOT NULL DEFAULT 0,
name VARCHAR(255) NOT NULL,
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
description VARCHAR(1000),
PRIMARY KEY(category_id),
CONSTRAINT UNIQUE(parent_category_id, name)
) TYPE=InnoDB;

Thanks,

Sam

Entity Relationship Diagram Software
Any good entity relationship diagram software .

Folder-and-file-like Object Relationship Implementation
I'd like to develop something which has a concept like the
folders-and-files in your PC.

A folder can have subfolders and/or files. The subfolders themselves
have the same characteristics.

This characteristic can also be found in Google Newsgroup, aka Usenet.
A group can have many postings and many other groups.

I want to reapply this concept for listing contents for categories,
not unlike the categories one can find in Yahoo. For example:

The root has many categories: Electronics, Art, Entertainment, etc.
Under Electronics there are subcategories such as cameras, washing
machine and refrigerators. However, here's the twist:

When I select Electronics, I want all items under all subcategories to
be listed as well. That means I need to traverse to every descendant
categories to view all items under them.

If I start out with two tables, CategoryTable and ItemTable, how
should I design their relationship and how should the SQL query be
constructed ?