RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Hello, I've tried your way, but my SQL reports error, that I have specified more than ONE primary key, so I can't do the primary key on both fields. Is that a mySQL 3.X limitation ? My tables are ISAM. Following your advice, I've deleted the relation_person_carID, so only cardID and personID are left i nthe table, with no primary key. Is it a good idea, and is it safe, not to have a primary key ? Cheers, Damien COLA -Message d'origine- 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) something like : table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 14, 2002 9:16 AM To: Adolfo Bello; [EMAIL PROTECTED] Subject: RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? Hello, I've tried your way, but my SQL reports error, that I have specified more than ONE primary key, so I can't do the primary key on both fields. Is that a mySQL 3.X limitation ? My tables are ISAM. Following your advice, I've deleted the relation_person_carID, so only cardID and personID are left i nthe table, with no primary key. Is it a good idea, and is it safe, not to have a primary key ? Cheers, Damien COLA -Message d'origine- 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) something like : table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Hello, you're right, creating the table with 2 primary keys works fine. What I was doing is try to convert my personID and carID fields into primary, and it says: Error MySQL : Invalid SQL: ALTER TABLE `varmalinalliax`.`tgl_rel_rest_card` CHANGE `restID` `restID` INT (11) DEFAULT '0' NOT NULL , CHANGE `cardID` `cardID` INT (11) DEFAULT '0' NOT NULL , ADD PRIMARY KEY(`restID`), ADD PRIMARY KEY(`cardID`) Error My SQL number: 1068 (Multiple primary key defined) What simple query could I make to transform the fields in primary ? I don't have access to the mysql server via command line, I use a php package (eSKUeL, it's like phpMyAdmin) Cheers, Damien COLA -Message d'origine- Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Using the ALTER statement: Step 1.- DROP the primary key Step 2.- ADD the new primary key Adolfo -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 14, 2002 9:54 AM To: Adolfo Bello; [EMAIL PROTECTED] Subject: RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? Hello, you're right, creating the table with 2 primary keys works fine. What I was doing is try to convert my personID and carID fields into primary, and it says: Error MySQL : Invalid SQL: ALTER TABLE `varmalinalliax`.`tgl_rel_rest_card` CHANGE `restID` `restID` INT (11) DEFAULT '0' NOT NULL , CHANGE `cardID` `cardID` INT (11) DEFAULT '0' NOT NULL , ADD PRIMARY KEY(`restID`), ADD PRIMARY KEY(`cardID`) Error My SQL number: 1068 (Multiple primary key defined) What simple query could I make to transform the fields in primary ? I don't have access to the mysql server via command line, I use a php package (eSKUeL, it's like phpMyAdmin) Cheers, Damien COLA -Message d'origine- Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
The way I've always done it, and I wonder if I am not doing lots of things wrong: i have 2 tables, one person and one address for a one to one relation, the way I do it: table person personID primary int 11 autoincrement addressID int 11 table address addressID primary int 11 autoincrement if it's a one ot many relationship i do it like that: table person personID primary int 11 autoincrement table car addressID primary int 11 autoincrement personID int 11 NOW, reading this great mailinglist, I've come to believe the right way to do it that works in both relationship types : table person personID primary int 11 autoincrement table car carID primary int 11 autoincrement table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 I understand it's closer to the real way to represent relations in database design, but I wonder what else can be done with mySQL to simplify the work. for example, should I make something special with the type of fields personID and addressID in table relation_person_car ? another example, it would be easier to name all the primary key fields 'id' instead of '*nameOfTheTable*ID', but what would be the backdraws when coding queries and server scripts ? Any critics on my beginner way of handling relations is most welcome. Cheers, Damien COLA Cordialement, __ Alliax ~CV : http://LingoParadise.com/cv.php Un site pour Toulon : http://www.ToulonParadise.com Un site pour Renaud : http://www.rfaucilhon.com Un site pour Director : http://www.LingoParadise.com Un site pour Harmonica: http://www.LingoParadise.com/mp3 -Message d'origine- De : Michael T. Babcock [mailto:[EMAIL PROTECTED]] Envoyé : lundi 9 décembre 2002 15:15 À : [EMAIL PROTECTED] Objet : Re: QUICK: What is the optimal way to store opening times ? On Fri, Dec 06, 2002 at 05:54:53PM +0100, Alliax wrote: I have one question : if I go the RestTimes route, that is having a row per day and so 7 row per restaurants : can I, in one SQL request, know if THAT restaurant is open or close now ? I now there are NOW() function in SQL and probably many others, but I am not at ease to use them since I have pretty basic SQL skills. This should be a personal work assignment for you, but try: SELECT * FROM RestTimes WHERE RestID = ... AND OpenTime now() and CloseTime now(); FWIW, you'll have to do a calculation in there such that OpenTime is midnight today + seconds from day offset. Its not difficult; find some calendaring code for examples. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
I would really prefer a little variation of the third way you mention: 1) table person personID primary int 11 autoincrement 2) table car carID primary int 11 autoincrement 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) Adolfo -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 8:35 PM To: [EMAIL PROTECTED] Subject: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ? The way I've always done it, and I wonder if I am not doing lots of things wrong: i have 2 tables, one person and one address for a one to one relation, the way I do it: table person personID primary int 11 autoincrement addressID int 11 table address addressID primary int 11 autoincrement if it's a one ot many relationship i do it like that: table person personID primary int 11 autoincrement table car addressID primary int 11 autoincrement personID int 11 NOW, reading this great mailinglist, I've come to believe the right way to do it that works in both relationship types : table person personID primary int 11 autoincrement table car carID primary int 11 autoincrement table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 I understand it's closer to the real way to represent relations in database design, but I wonder what else can be done with mySQL to simplify the work. for example, should I make something special with the type of fields personID and addressID in table relation_person_car ? another example, it would be easier to name all the primary key fields 'id' instead of '*nameOfTheTable*ID', but what would be the backdraws when coding queries and server scripts ? Any critics on my beginner way of handling relations is most welcome. Cheers, Damien COLA Cordialement, __ Alliax ~CV : http://LingoParadise.com/cv.php Un site pour Toulon : http://www.ToulonParadise.com Un site pour Renaud : http://www.rfaucilhon.com Un site pour Director : http://www.LingoParadise.com Un site pour Harmonica: http://www.LingoParadise.com/mp3 -Message d'origine- De : Michael T. Babcock [mailto:[EMAIL PROTECTED]] Envoyé : lundi 9 décembre 2002 15:15 À : [EMAIL PROTECTED] Objet : Re: QUICK: What is the optimal way to store opening times ? On Fri, Dec 06, 2002 at 05:54:53PM +0100, Alliax wrote: I have one question : if I go the RestTimes route, that is having a row per day and so 7 row per restaurants : can I, in one SQL request, know if THAT restaurant is open or close now ? I now there are NOW() function in SQL and probably many others, but I am not at ease to use them since I have pretty basic SQL skills. This should be a personal work assignment for you, but try: SELECT * FROM RestTimes WHERE RestID = ... AND OpenTime now() and CloseTime now(); FWIW, you'll have to do a calculation in there such that OpenTime is midnight today + seconds from day offset. Its not difficult; find some calendaring code for examples. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
