MYSQL normalization

artisticre

I am trying to understand normalization. Here are the 3 tables I have that I am trying to insert info into but cannot get it right.

CREATE TABLE Users (
userId tinyint(4) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
compId tinyint(4) NOT NULL default '0',
PRIMARY KEY (userId,compId)
) TYPE=MyISAM;

CREATE TABLE Urls (
urlId tinyint(4) NOT NULL auto_increment,
userId tinyint(4) NOT NULL default '0',
url varchar(50) NOT NULL default '',
PRIMARY KEY (urlId,userId)
) TYPE=MyISAM;

CREATE TABLE Company (
compId tinyint(4) NOT NULL auto_increment,
company varchar(50) NOT NULL default '',
address varchar(50) NOT NULL default '',
PRIMARY KEY (compId)
) TYPE=MyISAM;

Can someone show me the code to insert into these tables.?

thanks

Rakim

try using only 1 primary key i dont really know mysql i only started with it 4 days ago actualy but i know alot about databases in themselves "MS Access" things like that and i know primary key is something that is unique to that record and there can be only one of them ! think about it abit !

Rakim

o ur not asking that 😃 sry well umm try this..

if u used phpmyadmin then go to the table click insert insert one any one and then at the top it will tell you the query open that and click on where it says ShowPHPcode and it will show you the query you need to insert to the tables

and the code on a php page would be .....

$query = "INSERT INTO members (filed 1 , field2 , etc)"
and thats about all there is to it so im told i have only got it working 2 times in 4 days in like 20 tries lol.....

http://www.irc-unit.com check it out thats my code !

artisticre

Basically I need to know how to insert an idea in the primary key as well as the referenced ID number in another table
I know this is primary key/foreign key stuff but not sure how to make it work
any ideas?

YappyDog

You need to insert your data into the Company table first. That will create the compId values you need to insert into the Users table. Which will then create the userId values you need to insert into the Urls table.

You can't do it all in one statement.

artisticre

Does this look right?

$Company = "INSERT INTO Company (company, address) VALUES ('$company', '$address')";
$CompanyResult = mysql_query($Company) or die(mysql_error());
$compId = mysql_insert_id();

$User = "INSERT INTO Users (name) VALUES ('$user')";
$UserResult = mysql_query($User) or die(mysql_error());
$userId = mysql_insert_id();

$Company = "INSERT INTO Company (company, address) VALUES ('$company', '$address')";
$CompanyResult = mysql_query($Company) or die(mysql_error());
$compId = mysql_insert_id();

$URL = "INSERT INTO Urls (url) VALUES ('$url')";
$URLResult = mysql_query($URL) or die(mysql_error());
$urlId = mysql_insert_id();

$CompSQL = "INSERT INTO Company (compId) VALUES ($compId)";
$InsertResult = mysql_query($CompSQL) or die(mysql_error());

$UserSQL = "INSERT INTO User (userId, compId) VALUES (userId, $compid";
$InsertResult = mysql_query($UserSQL) or die(mysql_error());

$CompSQL = "INSERT INTO Company (compId) VALUES ($compId)";
$InsertResult = mysql_query($CompSQL) or die(mysql_error());

$UrlsSQL = "INSERT INTO Urls (urlId, userId) VALUES ($urlId, $userId)";
$InsertResult = mysql_query($UrlsSQL) or die(mysql_error());

ahundiak

I don't think you quite have the idea yet.
Take a look at this article:
http://www.phpbuilder.com/columns/barry20000731.php3
It actually does almost exactly what you are trying to do.

artisticre

Is this better?

$Company = "INSERT INTO Company (compName, compAdd) VALUES ('$compName', '$compAdd')";
$CompanyResult = mysql_query($Company) or die(mysql_error());
$compID = mysql_insert_id();

$Users = "INSERT INTO Users (compID, userName) VALUES ('$compID','$userName')";
$UserResult = mysql_query($Users) or die(mysql_error());
$userID = mysql_insert_id();

$URLS = "INSERT INTO URLS (userID, urlName) VALUES ('$userID','$urlName')";
$URLSResult = mysql_query($URLS) or die(mysql_error());
$urlID = mysql_insert_id();

ahundiak

Yep.

And if you have not already done it, Change PRIMARY KEY (userId,compId)
To just PRIMARY KEY (userId).

Same for the url table.

artisticre

This should reduce redundancy correct? Is it error checking that will reduce it cause without error checking I still get redundancy

as you probably can tell I am new to MYSQL & PHP..teaching myself for the benefit of knowing

thanks for your help