re:database tables relations advice

I am quite new to database designs, I have a problem in my design...I
can actually feel it, but I am not quite sure if there is a feature in
mysql or I have to solve it with programming.

Example:
CREATE TABLE A (
user_id int(16) NOT NULL auto_increment,
..........other datas
PRIMARY KEY (user_id)
);

CREATE TABLE B (
user_id int(16) NOT NULL auto_increment,
..............other datas
PRIMARY KEY (contact_id)
);

Question:
How can I declare that the user_id in my 1st table is related to user_id
in the 2nd table...actually I prefer to have it exactly the same user_id
in both tables....I think if those 2 entries are the same it will be
great, but I am not sure how to achieve this.

P:S
-Ofcourse I know that I can extract it from TABLE A and save it in TABLE
B....but is that a way to go???Because this issue arise in couple of
tables in my data structure that I am tending to use in my application(web).
-I also know that its possible to make just 1 big table with lots of
columns....but I read its not a good database design...

----->>>>>please advice, running out of ideas :-(

Thanks......


--
Extra details:
OSS:Gentoo Linux-2.6.25-r8
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash
Typo:40WPM
url:http://mambo-tech.net


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: re:database tables relations advice

--------------090607040504000509020901
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

This is solved by using FOREIGN KEY but I'm not sure if MySQL have them
present or just planned for some future release.

mrfroasty napsal(a):
> I am quite new to database designs, I have a problem in my design...I
> can actually feel it, but I am not quite sure if there is a feature in
> mysql or I have to solve it with programming.
>
> Example:
> CREATE TABLE A (
> user_id int(16) NOT NULL auto_increment,
> ..........other datas
> PRIMARY KEY (user_id)
> );
>
> CREATE TABLE B (
> user_id int(16) NOT NULL auto_increment,
> ..............other datas
> PRIMARY KEY (contact_id)
> );
>
> Question:
> How can I declare that the user_id in my 1st table is related to user_id
> in the 2nd table...actually I prefer to have it exactly the same user_id
> in both tables....I think if those 2 entries are the same it will be
> great, but I am not sure how to achieve this.
>
> P:S
> -Ofcourse I know that I can extract it from TABLE A and save it in TABLE
> B....but is that a way to go???Because this issue arise in couple of
> tables in my data structure that I am tending to use in my application(web).
> -I also know that its possible to make just 1 big table with lots of
> columns....but I read its not a good database design...
>
> ----->>>>>please advice, running out of ideas :-(
>
> Thanks......
>
>
>

--

S pozdravem

Daniel Tlach
Freelance webdeveloper

Email: mail@danaketh.com
ICQ: 160914875
MSN: danaketh@hotmail.com
Jabber: danaketh@jabbim.cz


--------------090607040504000509020901--

Re: re:database tables relations advice

maruti wrote:
> hey lemme knoe, how many columns are you planning for?
> y not put table B columns in table A??
> if you are least bothered about data normalizations, simply go the way
> you want to.
> userId Name Age Age Sex Occupation Location
> all the data which has correspondence with userid can be put in a
> single table..
> the case is if you dont've many related tables..
> pavan puligandla
> Microsoft has no beef with OpenSource.
>
>
> 2008/11/27 mrfroasty >
>
> maruti wrote:
> > hii,,
> > who said table with more no:of columns is a bad database design?
> > coming to your tables,
> > what do you wanna do??
> > if you want to pull out the data of 'X' userid from table B,
> then you
> > can use left join.
> > make sure that user ID of table A and userID of table B have same
> > data. to use joins, atleast one column(s) should be the same in both
> > tables.
> > let me know whether userID of table B is a foreign key of UserID of
> > table A or not?
> >
> > normalization in all cases doesnt work.
> > i'm using spreadsheet as my front end, so my tables are not even in
> > the second normal form..
> >
> > here are some excellent tutorials for joins;
> > http://www.tutorialspoint.com/mysql/mysql-using-joins.htm
> >
> http://dev.mysql.com/tech-resources/articles/intro-to-normal ization.html
> > hope these might help u alot..
> >
> >
> > pavan puligandla
> > Microsoft has no beef with OpenSource.
> >
> >
> > 2008/11/27 mrfroasty > > >>
> >
> > I am quite new to database designs, I have a problem in my
> design...I
> > can actually feel it, but I am not quite sure if there is a
> feature in
> > mysql or I have to solve it with programming.
> >
> > Example:
> > CREATE TABLE A (
> > user_id int(16) NOT NULL auto_increment,
> > ..........other datas
> > PRIMARY KEY (user_id)
> > );
> >
> > CREATE TABLE B (
> > user_id int(16) NOT NULL auto_increment,
> > ..............other datas
> > PRIMARY KEY (contact_id)
> > );
> >
> > Question:
> > How can I declare that the user_id in my 1st table is related to
> > user_id
> > in the 2nd table...actually I prefer to have it exactly the same
> > user_id
> > in both tables....I think if those 2 entries are the same it
> will be
> > great, but I am not sure how to achieve this.
> >
> > P:S
> > -Ofcourse I know that I can extract it from TABLE A and save
> it in
> > TABLE
> > B....but is that a way to go???Because this issue arise in
> couple of
> > tables in my data structure that I am tending to use in my
> > application(web).
> > -I also know that its possible to make just 1 big table
> with lots of
> > columns....but I read its not a good database design...
> >
> > ----->>>>>please advice, running out of ideas :-(
> >
> > Thanks......
> >
> >
> > --
> > Extra details:
> > OSS:Gentoo Linux-2.6.25-r8
> > profile:x86
> > Hardware:msi geforce 8600GT asus p5k-se
> > location:/home/muhsin
> > language(s):C/C++,VB,VHDL,bash
> > Typo:40WPM
> > url:http://mambo-tech.net
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> Quote:
> make sure that user ID of table A and userID of table B have same
> data.
>
> #That is actually what I am looking for, but I dunno how to achieve
> that.If I can have those 2 user_id columns with the same data, my
> problem is technically solved.But as I said earlier I am limited to
> database skills, its my first application that involves mysql &&
> php....do I need those foreign key stuffs, to have those 2 columns
> with
> the same data??
>
> --
> Extra details:
> OSS:Gentoo Linux-2.6.25-r8
> profile:x86
> Hardware:msi geforce 8600GT asus p5k-sehave
> location:/home/muhsin
> language(s):C/C++,VB,VHDL,bash
> Typo:40WPM
> url:http://mambo-tech.net
>
>

That issue arise like 3 times in my data structure....having all related
data in one table I might ended up with a table of ~20 columns, it will
be too much and probably bad programming practise.....I will have a look
over the net about FOREIGN KEY....didnt know what it is, may be its the
solution...

Thanks for the input....



--
Extra details:
OSS:Gentoo Linux-2.6.25-r8
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash
Typo:40WPM
url:http://mambo-tech.net


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: re:database tables relations advice {solved}

danaketh wrote:
> This is solved by using FOREIGN KEY but I'm not sure if MySQL have
> them present or just planned for some future release.
>
> mrfroasty napsal(a):
>> I am quite new to database designs, I have a problem in my design...I
>> can actually feel it, but I am not quite sure if there is a feature in
>> mysql or I have to solve it with programming.
>>
>> Example:
>> CREATE TABLE A (
>> user_id int(16) NOT NULL auto_increment,
>> ..........other datas
>> PRIMARY KEY (user_id)
>> );
>>
>> CREATE TABLE B (
>> user_id int(16) NOT NULL auto_increment,
>> ..............other datas
>> PRIMARY KEY (contact_id)
>> );
>>
>> Question:
>> How can I declare that the user_id in my 1st table is related to user_id
>> in the 2nd table...actually I prefer to have it exactly the same user_id
>> in both tables....I think if those 2 entries are the same it will be
>> great, but I am not sure how to achieve this.
>>
>> P:S
>> -Ofcourse I know that I can extract it from TABLE A and save it in TABLE
>> B....but is that a way to go???Because this issue arise in couple of
>> tables in my data structure that I am tending to use in my application(web).
>> -I also know that its possible to make just 1 big table with lots of
>> columns....but I read its not a good database design...
>>
>> ----->>>>>please advice, running out of ideas :-(
>>
>> Thanks......
>>
>>
>>
>
> --
>
> S pozdravem
>
> Daniel Tlach
> Freelance webdeveloper
>
> Email: mail@danaketh.com
> ICQ: 160914875
> MSN: danaketh@hotmail.com
> Jabber: danaketh@jabbim.cz

Thanks for the input...after some small research I came across this link
http://articles.techrepublic.com.com/5100-10878_11-6035435.h tml
.....after that I changed my database to something similar to :

CREATE TABLE user_profile (
user_id int(16) NOT NULL auto_increment,
................other datas
PRIMARY KEY (user_id)
)ENGINE=INNODB;

CREATE TABLE user_contact (
user_id int(16) NOT NULL auto_increment,
..................other datas
INDEX (user_id),
FOREIGN KEY (user_id) REFERENCES user_profile (user_id),
PRIMARY KEY (user_id)
) ENGINE=INNODB;

That I think problem solved for now...thanks alot :-)

--
Extra details:
OSS:Gentoo Linux-2.6.25-r8
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash
Typo:40WPM
url:http://mambo-tech.net


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: re:database tables relations advice

Mr. Froasty,

From your note, it sounds like you want to use foreign keys; as Daniel
pointed out. I think an example would be helpful here. The subject of
foreign keys is bigger than a bread box so I'll just touch on the pieces
I think you'll find helpful. There is all sorts of literature scattered
about the web if you want to know more. Let's start with a fictional
case:

I work for a company with multiple departments each of which have one or
more employees. I would like a relational data structure to capture
departmental and employee information as well as preserve the
relationship between the two.

Make sense?

I create two tables: `Department` and `Employee`. Each table has a
primary key (as you illustrated in your example), which is unique per
record. I add a column in Employee that holds the primary key
of the employee's associated department. I then create a
relation between the two tables to indicate there is a relationship.

--Create the Department table
CREATE TABLE Department (
IDDepartment INT NOT NULL AUTO_INCREMENT,=20
Name VARCHAR(35),
PRIMARY KEY (IDDepartment)
) ENGINE =3D InnoDB;

--Create the Employee table and simultaneously the=20
--relation to Department
CREATE TABLE Employee (
IDEmployee INT NOT NULL AUTO_INCREMENT,=20
idDepartment INT NOT NULL,
Name VARCHAR(35),
PRIMARY KEY (IDEmployee),
INDEX IDX_idDepartment (idDepartment),
FOREIGN KEY (idDepartment) REFERENCES Department(idDepartment)=20
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE =3D InnoDB;

MySQL can do all of this provided you're using the InnoDB storage
engine. MySQL's documentation has some helpful information on the
subject - see link below.

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-key s.html

With me so far?

A few points specific to MySQL:

(1) Whatever field you chose as your foreign key, needs an index.
(2) You can add foreign keys after a table has been created using an
ALTER statement.
(3) The option ON DELETE CASCADE means that whenever the parent record
(i.e., the department) is deleted the related employees will be deleted
too.
(4) The option ON UPDATE CASCADE means that whenver the parent's key
record (i.e., the department) is updated the related foreign key record
will be updated too.
(5) There are options other than ON UPDATE and ON DELETE. Give'm a look.

Good luck, and welcome to the DB development club.

Cheers,
Adam

-----Original Message-----
From: mrfroasty [mailto:mrfroasty@gmail.com]=20
Sent: Thursday, November 27, 2008 5:19 AM
To: php-db@lists.php.net
Subject: [PHP-DB] re:database tables relations advice

I am quite new to database designs, I have a problem in my design...I
can actually feel it, but I am not quite sure if there is a feature in
mysql or I have to solve it with programming.

Example:
CREATE TABLE A (
user_id int(16) NOT NULL auto_increment,
..........other datas
PRIMARY KEY (user_id)
);

CREATE TABLE B (
user_id int(16) NOT NULL auto_increment,
..............other datas
PRIMARY KEY (contact_id)
);

Question:
How can I declare that the user_id in my 1st table is related to user_id
in the 2nd table...actually I prefer to have it exactly the same user_id
in both tables....I think if those 2 entries are the same it will be
great, but I am not sure how to achieve this.

P:S
-Ofcourse I know that I can extract it from TABLE A and save it in TABLE
B....but is that a way to go???Because this issue arise in couple of
tables in my data structure that I am tending to use in my
application(web).
-I also know that its possible to make just 1 big table with lots of
columns....but I read its not a good database design...

----->>>>>please advice, running out of ideas :-(

Thanks......


--=20
Extra details:
OSS:Gentoo Linux-2.6.25-r8
profile:x86
Hardware:msi geforce 8600GT asus p5k-se
location:/home/muhsin
language(s):C/C++,VB,VHDL,bash
Typo:40WPM
url:http://mambo-tech.net


--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: re:database tables relations advice

------=_Part_40441_4149601.1227813581648
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thu, Nov 27, 2008 at 1:36 PM, Fortuno, Adam
wrote:

> Mr. Froasty,
>
> From your note, it sounds like you want to use foreign keys; as Daniel
> pointed out. I think an example would be helpful here. The subject of
> foreign keys is bigger than a bread box so I'll just touch on the pieces
> I think you'll find helpful. There is all sorts of literature scattered
> about the web if you want to know more. Let's start with a fictional
> case:
>
> I work for a company with multiple departments each of which have one or
> more employees. I would like a relational data structure to capture
> departmental and employee information as well as preserve the
> relationship between the two.
>
> Make sense?
>
> I create two tables: `Department` and `Employee`. Each table has a
> primary key (as you illustrated in your example), which is unique per
> record. I add a column in Employee that holds the primary key
> of the employee's associated department. I then create a
> relation between the two tables to indicate there is a relationship.
>
> --Create the Department table
> CREATE TABLE Department (
> IDDepartment INT NOT NULL AUTO_INCREMENT,
> Name VARCHAR(35),
> PRIMARY KEY (IDDepartment)
> ) ENGINE = InnoDB;
>
> --Create the Employee table and simultaneously the
> --relation to Department
> CREATE TABLE Employee (
> IDEmployee INT NOT NULL AUTO_INCREMENT,
> idDepartment INT NOT NULL,
> Name VARCHAR(35),
> PRIMARY KEY (IDEmployee),
> INDEX IDX_idDepartment (idDepartment),
> FOREIGN KEY (idDepartment) REFERENCES Department(idDepartment)
> ON DELETE CASCADE
> ON UPDATE CASCADE
> ) ENGINE = InnoDB;
>
> MySQL can do all of this provided you're using the InnoDB storage
> engine. MySQL's documentation has some helpful information on the
> subject - see link below.
>
> http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-key s.html
>
> With me so far?
>
> A few points specific to MySQL:
>
> (1) Whatever field you chose as your foreign key, needs an index.
> (2) You can add foreign keys after a table has been created using an
> ALTER statement.
> (3) The option ON DELETE CASCADE means that whenever the parent record
> (i.e., the department) is deleted the related employees will be deleted
> too.
> (4) The option ON UPDATE CASCADE means that whenver the parent's key
> record (i.e., the department) is updated the related foreign key record
> will be updated too.
> (5) There are options other than ON UPDATE and ON DELETE. Give'm a look.
>
> Good luck, and welcome to the DB development club.
>
> Cheers,
> Adam
>
> -----Original Message-----
> From: mrfroasty [mailto:mrfroasty@gmail.com]
> Sent: Thursday, November 27, 2008 5:19 AM
> To: php-db@lists.php.net
> Subject: [PHP-DB] re:database tables relations advice
>
> I am quite new to database designs, I have a problem in my design...I
> can actually feel it, but I am not quite sure if there is a feature in
> mysql or I have to solve it with programming.
>
> Example:
> CREATE TABLE A (
> user_id int(16) NOT NULL auto_increment,
> ..........other datas
> PRIMARY KEY (user_id)
> );
>
> CREATE TABLE B (
> user_id int(16) NOT NULL auto_increment,
> ..............other datas
> PRIMARY KEY (contact_id)
> );
>
> Question:
> How can I declare that the user_id in my 1st table is related to user_id
> in the 2nd table...actually I prefer to have it exactly the same user_id
> in both tables....I think if those 2 entries are the same it will be
> great, but I am not sure how to achieve this.
>
> P:S
> -Ofcourse I know that I can extract it from TABLE A and save it in TABLE
> B....but is that a way to go???Because this issue arise in couple of
> tables in my data structure that I am tending to use in my
> application(web).
> -I also know that its possible to make just 1 big table with lots of
> columns....but I read its not a good database design...
>
> ----->>>>>please advice, running out of ideas :-(
>
> Thanks......
>
>
> --
> Extra details:
> OSS:Gentoo Linux-2.6.25-r8
> profile:x86
> Hardware:msi geforce 8600GT asus p5k-se
> location:/home/muhsin
> language(s):C/C++,VB,VHDL,bash
> Typo:40WPM
> url:http://mambo-tech.net
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
A couple of quick notes to add here:

1. MySQL supports FKs when using the INNODB engine, so you will need to
change the engine type if the tables are anything else

2. It is possible to use the application to handle the keys instead of the
database, it involves more work around key checking / validation before
creating or updating records, but it can be done if the INNODB table type is
not accessible to you for some reason. Inserting / reading from the table
would be handled by an order precedence where you first select something
from something like the users table to get the user id (or store it in
session when logging the user in) and then adding that key to the other
table.

--

Bastien

Cat, the other other white meat

------=_Part_40441_4149601.1227813581648--

Re: re:database tables relations advice

On Thu, Nov 27, 2008 at 11:19 AM, Bastien Koert wrote:
> 2. It is possible to use the application to handle the keys instead of the
> database, it involves more work around key checking / validation before
> creating or updating records, but it can be done if the INNODB table type is
> not accessible to you for some reason.

Yes, but generally foreign key constraints are preferable. The
database server is compiled in native code, so it'll be much faster at
handling this issue; and it may avoid the necessity of your script
submitting multiple queries to resolve the foreign key issues. A
bigger issue though is that the implementation of foreign key
constraints on the server will have been exhaustively tested.
Reinventing the wheel in your own PHP code may introduce bugs that
cause loss or damage to your data.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: re:database tables relations advice

------=_Part_65495_22913409.1228095565962
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Fri, Nov 28, 2008 at 5:52 PM, Fergus Gibson wrote:

> On Thu, Nov 27, 2008 at 11:19 AM, Bastien Koert wrote:
> > 2. It is possible to use the application to handle the keys instead of
> the
> > database, it involves more work around key checking / validation before
> > creating or updating records, but it can be done if the INNODB table type
> is
> > not accessible to you for some reason.
>
> Yes, but generally foreign key constraints are preferable. The
> database server is compiled in native code, so it'll be much faster at
> handling this issue; and it may avoid the necessity of your script
> submitting multiple queries to resolve the foreign key issues. A
> bigger issue though is that the implementation of foreign key
> constraints on the server will have been exhaustively tested.
> Reinventing the wheel in your own PHP code may introduce bugs that
> cause loss or damage to your data.
>

Correct, I was merely offering an alternative if the INNODB / FK constraints
are not available. I have to do that with ASP code that I maintain, but its
a real bitch to manage

--

Bastien

Cat, the other other white meat

------=_Part_65495_22913409.1228095565962--