Re: problems with relationships created by mysql
oops..sorryproblem seems to be with base...so i meant to post it in base mailing list On Mon, May 13, 2013 at 9:37 AM, rounak jain rounak.m...@gmail.com wrote: I have connected MySql to Base. I created some relationships in MySql. They are properly visible in the Relationships chart in Base. See image. http://imgur.com/qtM8fVN,jwvJpen Other than that, they are non-existent. For example, while I try to create a sub-form, I see that the option to create subform via existing relationship is disabled. See image. http://imgur.com/qtM8fVN,jwvJpen#1 Kindly guide me.
1 to many relationships
hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table i have Customers /Issues as tables, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Gerald L. Clark said: Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation I beg to differ. It would be a many to many if several customers were related to the same issue. If each customer can have one or more issues, but no issue can 'belong' to more than one customer then its 1 to many and you can put the customer ID in the issues table. If several customers can have the same issue (row) then you will need an intermediate table which has two columns: customerID and issueID. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table i have Customers /Issues as tables, As William said you have a 1 to many relationship so you can get away with this as a very simple example: create table customers (customerid int auto_increment primary key, customername varchar(255)); create table issues (issueid int auto_increment primary key, issuetitle varchar(255), customerid int); then you have a simple inner join to find the issues that each customer has. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. Since there have not been a lot of responses I decided to jump in. It sounds to me like we have real-world object behavior mixed up with the data model. Example RFQ items ALWAYS have a partID If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. Why? Who or what is enforcing this? Can we look at overall object behavior, then come up with a model that supports the behavior with no preconceptions of table structure. I assume that an RFQ item is a document (paper or eletronic). What does one look like? From your description it will always have a partID and may have an inventoryID. Who populates these fields? Why is there a redundancy in the first place? Who checks to see that the direct partID matches the derived partID? How about leaving partID and inventoryID out of the RFQ table, and adding an association table that relates a RFQ to either a partID or an inventoryID. An attribute of this table would distinguish partID from an inventoryID. Business logic would ensure that only one entry gets into this table per RFQ, and could also validate that the direct partID matches the derived partID I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Same issue here. Remove the IDs from the quote and RFQ table and create another association table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General DB Design Question - How to avoid redundancy in table relationships
These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice.
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott, I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. It looks like the kind of problem database schemas are meant to _avoid_. >From your description it seems you have ... part ( partID PRIMARY KEY ) inventory ( inventoryID PRIMARY KEY, partID FOREIGN KEY REFERENCES part.partID ) RFQ ( rfqID PRIMARY KEY??? (I assume), partID FOREIGN KEY references part.partID, inventoryID NULL LOOKS UP inventory.inventoryID ) according to which ... (i) a RFQ item can reference a partID which is not in inventory, (ii) even if a RFQ partID is in inventory, it may show up in RFQ paired with a different invcentoryID, but (iii) if [ii] occurs, it indicates an error which is plumb crazy--if [ii] is an error, the schema should disallow it. The business rules embedded in this schema contain a contradiction. If it were my project, I'd conclude that it's time to sit down with the client. But perhaps we need more info? PB Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote: Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? I think so. But first, a tip: When you get an error like that from InnoDB, try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions of MySQL). Part of the output of this statement will likely contain more detail about the error. In this case, the error is my fault. :-) The referenced column and the referencing column must have the same data type, and I said that SERIAL was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column, so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also NOT NULL, you might as well make schedule_id NOT NULL, too. These definitions worked for me: CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_dateDATE NOT NULL ) ENGINE = InnoDB; CREATE TABLE registration ( idSERIAL
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote: Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? I think so. But first, a tip: When you get an error like that from InnoDB, try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions of MySQL). Part of the output of this statement will likely contain more detail about the error. In this case, the error is my fault. :-) The referenced column and the referencing column must have the same data type, and I said that SERIAL was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column, so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also NOT NULL, you might as well make schedule_id NOT NULL, too. These definitions worked for me: CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_dateDATE NOT NULL ) ENGINE = InnoDB; CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstname
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ignores foreign key relationships between tables?
I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Child-Parent Relationships with 2 Parents
David Blomstrom wrote: I just thought of a possible solution. Instead of listing all the phyla, orders, classes, families, genera and species in one big table, create separate tables for each taxonomic level. Each taxon would then have two parents. the genus Sus' (pigs) parents would be both the subfamily Suinae and family Suidae. But the genus Panthera doesn't belong to a subfamily, so its family (Felidae) would be listed as both Parent and Parent2. NAME | PARENT | PARENT2 Sus | Suinae | Suidae Panthera | Felidae | Felidae Then I could write PHP scripts focusing on either Parent or Parent2, depending on what I want to do. Does this sound sensible? Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs I'll be honest, I forgot most of that stuff from Biology. However, in general if an item can have two or more parents, then you can use a separate table to represent that relationship. For example: ParentTable --- ParentId Attribute1 Attribute2 ... RelationshipTable ParentId ChildId ChildTable ChildId Attribute1 Attribute2 ... Now, the RelationshipTable can have zero to many relationships between a child and a parent. You can even add a Label column in there if you want to label the relationship type somehow. And it may be better to create separate tables for each taxonomic level, like you said. Eventually one day you may want to have attributes (columns) of a species, that may be different from attributes (columns) of a genus. -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Child-Parent Relationships with 2 Parents
Suppose I want to create an animal kingdom database
that gives me the option of displaying the following
taxonomic hierarchies:
1) A bread crumbs navigation string, including ALL
taxons; e.g. Animalia (kingdom) Chordata (phylum)
Vertebrata (a SUBphylum) Mammalia (class) Eutheria
(a SUBclass)
2) A bread crumbs nav string that excludes taxons
beginning with SUB; e.g. Animalia Chordata
Mammalia
3) Lists of various taxons' children, including ALL
taxons; e.g.
a. CHORDATA (phylum)
b. Vertebrata (subphylum)
b. Urochordata (subphylum)
4) Lists of taxons' children, excluding SUBorders,
SUBfamilies, etc.; e.g.
a. CHORDATA (phylum)
b. Mammalia (class)
b. Aves (class)
b. Reptilia (class), etc.
5. A combination that looks like this:
a. CHORDATA (phylum)
b. VERTEBRATA
c. Mammalia
c. Aves
c. Reptilia
c. Amphibia
b. UROCHORDATA
And if you clicked on Vertebrata, you'd see only its
children, like this:
Mammalia
Aves
Reptilia
Amphibia
What's the best way to organize a database to achieve
this flexibility? My current child-parent relationship
generally works, but I think I erred in treating
SUBtaxons a little differently, assigning them parents
but not children. For example, the data below
illustrates how I assigned the phylum Chordata as the
parent of both the subphylum Vertebrata and the class
Mammalia.
Chordata | Animalia
Vertebrata | Chordata
Mammalia | Chordata
So clicking Chordata displays...
Vertebrata
Mammalia
...and clicking Mammalia displays mammal orders, but
clicking Vertebrata displays nothing.
Do you think I'll be able to write PHP scripts that do
what I want, or does it make more sense to reorganize
my database, assigning parents and children to every
taxon, then writing PHP scripts that somehow weed out
SUBtaxons when I don't want to display them?
A third option is to use a recursive array, something
I haven't learned how to do yet. If I try a recursive
array, I assume I'd have to modify my database so that
every taxon does indeed have a parent and a child.
If it helps, I posted the code for the two PHP scripts
I'm using to make my bread crumbs navigation and to
display the children of various taxons. Eventually,
I'll have to write a third script that will join
tables with additional information on distribution,
diet, etc.
But I think what I really need now is confirmation
that I should assign EVERY taxon a parent and child
(or advice to the contrary) and whether I'd be better
off sticking with a child-parent relationship or a
recursive array.
Thanks.
[PHP]
//
http://www.sitepoint.com/article/hierarchical-data-database/2
function display_children($parent, $level)
{
$result = mysql_query('SELECT Name FROM gzanimals as A
WHERE Parent=' . $parent . ';');
while ($row = mysql_fetch_array($result)) {
echo str_repeat(' ',$level).$row['Name'].\n;
display_children($row['Name'], $level+1);
}
}
function get_path($node) {
$result = mysql_query('SELECT Parent FROM gzanimals
'. 'WHERE Name='.$node.';');
$row = mysql_fetch_array($result);
$path = array();
if ($row['Parent']!='') {
$path[] = $row['Parent'];
$path = array_merge(get_path($row['Parent']),
$path);
}
return $path;
}
$mypath = get_path($mycode);
for($i=0;$icount($mypath);$i++){
echo a href=\index.php?taxon=.$mypath[$i].\
.$mypath[$i]. /a gt; ;
}
[/PHP]
* * * * * * * * * *
This is the script I'm using to display children of
various taxons:
[PHP]
?php
$result = mysql_query('select count(*) from
gzanimals');
if (($result) (mysql_result ($result , 0) 0)) {
} else {
die('Invalid query: ' . mysql_error());
}
{
$taxon = mysql_query (SELECT Name, Parent FROM
gzanimals AS A
WHERE Parent = '$_GET[taxon]');
echo 'div class=' . $_GET['taxon'] . '';
echo 'table';
//!-- BeginDynamicTable --
// while ($row = mysql_fetch_array ($taxon)) {
while ($row = mysql_fetch_array($taxon, MYSQL_ASSOC))
{
// mysql_fetch_array($taxons, MYSQL_ASSOC)
// tda
href=SitePointA.php?taxon=$taxon{$row[Name]}/a/td
echo EOD
tr
tda
href=index.php?taxon=$row[Name]{$row[Name]}/a/td
/tr
EOD;
}
}
echo '/table/div';
?
/td
/tr
/table
[/PHP]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Child-Parent Relationships with 2 Parents
I just thought of a possible solution. Instead of listing all the phyla, orders, classes, families, genera and species in one big table, create separate tables for each taxonomic level. Each taxon would then have two parents. the genus Sus' (pigs) parents would be both the subfamily Suinae and family Suidae. But the genus Panthera doesn't belong to a subfamily, so its family (Felidae) would be listed as both Parent and Parent2. NAME | PARENT | PARENT2 Sus | Suinae | Suidae Panthera | Felidae | Felidae Then I could write PHP scripts focusing on either Parent or Parent2, depending on what I want to do. Does this sound sensible? Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL + acquiring table relationships
Paul Wallace [EMAIL PROTECTED] wrote on 01/19/2005 06:50:48 PM: Was there a reply to this post? If so, I could have (did) missed it. Can you please resend? Rgds Paul. Sorry, but I never said (woops!) Which language I use depends on where I want to run the script and how often, and the fact that I am working in an M$ shop. I don't have any code I could share right now, maybe later (employer restrictions). What it boils down to is that you execute a SHOW CREATE TABLE query then scan the results looking for the lines that start with FOREIGN KEY. From that point it is simple to break the line down by syntax (ON, REFERENCES, a set of parentheses) to find the child field, the parent table, and the parent field. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, what language(s) are you using to parse this? Could you share the code? Thanks, Dave Merrill I prefer to parse the results of a SHOW CREATE TABLE... query. It's rather trivial to detect which rows in the result of that statement are your FOREIGN KEYS. They not only indicate which table(s) is/are this table's parent(s) but also which columns participate in each relationship. This technique does not rely on any particular connection library as the functionality is provided by MySQL itself. Once the INFORMATION_SCHEMA views are made available as of v5.x+ we will have an alternative to using the SHOW family of statements to extract schema metadata. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL + acquiring table relationships
Was there a reply to this post? If so, I could have (did) missed it. Can you please resend? Rgds Paul. Shawn, what language(s) are you using to parse this? Could you share the code? Thanks, Dave Merrill I prefer to parse the results of a SHOW CREATE TABLE... query. It's rather trivial to detect which rows in the result of that statement are your FOREIGN KEYS. They not only indicate which table(s) is/are this table's parent(s) but also which columns participate in each relationship. This technique does not rely on any particular connection library as the functionality is provided by MySQL itself. Once the INFORMATION_SCHEMA views are made available as of v5.x+ we will have an alternative to using the SHOW family of statements to extract schema metadata. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL + acquiring table relationships
Paul Wallace [EMAIL PROTECTED] wrote on 01/17/2005 12:04:13 AM: Hi, How can I, if at all, acquire table relationships - in particular one-to-may/many-to-one relationships? I have looked into the DatabaseMetaData object (Java) , but have not as yet been able to acquire the said relationship data. I post to this list as the ability to pull such such information may be vendor specific? (am using MYSQL 4.018). Can anyone tell me if MySQL is capable of serving up such information - particularly using JDBC (JConnector)) thanks and regards Paul. I prefer to parse the results of a SHOW CREATE TABLE... query. It's rather trivial to detect which rows in the result of that statement are your FOREIGN KEYS. They not only indicate which table(s) is/are this table's parent(s) but also which columns participate in each relationship. This technique does not rely on any particular connection library as the functionality is provided by MySQL itself. Once the INFORMATION_SCHEMA views are made available as of v5.x+ we will have an alternative to using the SHOW family of statements to extract schema metadata. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: MYSQL + acquiring table relationships
Shawn, what language(s) are you using to parse this? Could you share the code? Thanks, Dave Merrill I prefer to parse the results of a SHOW CREATE TABLE... query. It's rather trivial to detect which rows in the result of that statement are your FOREIGN KEYS. They not only indicate which table(s) is/are this table's parent(s) but also which columns participate in each relationship. This technique does not rely on any particular connection library as the functionality is provided by MySQL itself. Once the INFORMATION_SCHEMA views are made available as of v5.x+ we will have an alternative to using the SHOW family of statements to extract schema metadata. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL + acquiring table relationships
Hi, How can I, if at all, acquire table relationships - in particular one-to-may/many-to-one relationships? I have looked into the DatabaseMetaData object (Java) , but have not as yet been able to acquire the said relationship data. I post to this list as the ability to pull such such information may be vendor specific? (am using MYSQL 4.018). Can anyone tell me if MySQL is capable of serving up such information - particularly using JDBC (JConnector)) thanks and regards Paul.
MYSQL + acquiring table relationships
Hi, How can I, if at all, acquire table relationships - in particular one-to-may/many-to-one relationships? I have looked into the DatabaseMetaData object (Java) , but have not as yet been able to acquire the said relationship data. I post to this list as the ability to pull such such information may be vendor specific? (am using MYSQL 4.018). Can anyone tell me if MySQL is capable of serving up such information - particularly using JDBC JConnector) thanks and regards Paul.
newbie: relationships between fields
Dear list, I'm hoping to move to MySQL from Access, mostly so I can use my Mac to build databases. So far I've been able to set up MySQL, connect to it, and create databases and tables. Getting to this point has required a paradigm shift or two on my part (my first question after it was installed was, okay, how the hell do I open up the app and start work?). Now I'd like to create some relationships between the tables in my database. But I'm having some trouble getting my head around how to do this - probably because I'm working with an Access paradigm. Imagine I had two tables: CONTACT COMPANY contactID coID namename employer I want the field contact.employer to link to a specific company record. In Access I'd set up a lookup in the field contact.employer, linked to the field company.coid; how do I achieve the same results in MySQL? If anyone can answer this or point me to a good resource I will be very grateful. Thanks in advance, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: relationships between fields
Quoting Christian Kavanagh [EMAIL PROTECTED]: Dear list, I'm hoping to move to MySQL from Access, mostly so I can use my Mac to build databases. So far I've been able to set up MySQL, connect to it, and create databases and tables. Getting to this point has required a paradigm shift or two on my part (my first question after it was installed was, okay, how the hell do I open up the app and start work?). Now I'd like to create some relationships between the tables in my database. But I'm having some trouble getting my head around how to do this - probably because I'm working with an Access paradigm. Imagine I had two tables: CONTACT COMPANY contactID coID name name employer Christian! CONTACT company_id COMPANY contactID coID namename employer assuming your coID stands for company ID, tie these two fields together select * from CONTACT,COMPANY where COMPANY.coID = CONTACT.company_id try that for starters Richard I want the field contact.employer to link to a specific company record. In Access I'd set up a lookup in the field contact.employer, linked to the field company.coid; how do I achieve the same results in MySQL? If anyone can answer this or point me to a good resource I will be very grateful. Thanks in advance, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: relationships between fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | Dear list, | | I'm hoping to move to MySQL from Access, mostly so I can use my Mac to build databases. So far I've been able to set up MySQL, connect to it, and create databases and tables. Getting to this point has required a paradigm shift or two on my part (my first question after it was installed was, okay, how the hell do I open up the app and start work?). | | Now I'd like to create some relationships between the tables in my database. But I'm having some trouble getting my head around how to do this - probably because I'm working with an Access paradigm. Imagine I had two tables: | [snip] Perhaps a good session with a relational database book would help you break the 'Access' paradigm :) I have had good success with 'Database Design For Mere Mortals', Michael J. Hernandez, 0-201-69471-9. 'MySQL' by Paul DuBois will also give you a good start to RDBMS concepts and design. - -- /* All outgoing email scanned by AVG Antivirus */ Amer Neely, Softouch Information Services Home of Spam Catcher North Bay Information Technology Networking Group W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | PHP | MySQL | CGI programming for all data entry forms. We make web sites work! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (MingW32) Comment: For info see http://www.gnupg.org iEYEARECAAYFAkGaZlAACgkQ3RxspxLYVsWIlACgnWa+wSt1xO8QTws3cldjsI+3 suQAn0i5mmNVOMCBvY2bB4arjZQNKYVs =IyI1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie: relationships between fields
NaviCat search for it on google it will make your life much easier. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: Amer Neely [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 1:43 PM To: MySQL Cc: Christian Kavanagh Subject: Re: newbie: relationships between fields -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | Dear list, | | I'm hoping to move to MySQL from Access, mostly so I can use my Mac to build databases. So far I've been able to set up MySQL, connect to it, and create databases and tables. Getting to this point has required a paradigm shift or two on my part (my first question after it was installed was, okay, how the hell do I open up the app and start work?). | | Now I'd like to create some relationships between the tables in my database. But I'm having some trouble getting my head around how to do this - probably because I'm working with an Access paradigm. Imagine I had two tables: | [snip] Perhaps a good session with a relational database book would help you break the 'Access' paradigm :) I have had good success with 'Database Design For Mere Mortals', Michael J. Hernandez, 0-201-69471-9. 'MySQL' by Paul DuBois will also give you a good start to RDBMS concepts and design. - -- /* All outgoing email scanned by AVG Antivirus */ Amer Neely, Softouch Information Services Home of Spam Catcher North Bay Information Technology Networking Group W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | PHP | MySQL | CGI programming for all data entry forms. We make web sites work! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (MingW32) Comment: For info see http://www.gnupg.org iEYEARECAAYFAkGaZlAACgkQ3RxspxLYVsWIlACgnWa+wSt1xO8QTws3cldjsI+3 suQAn0i5mmNVOMCBvY2bB4arjZQNKYVs =IyI1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Graphical program to describe table relationships
Hi All, This is more of a general SQL database question than a MySQL question, but since I use MySQL almost exclusively I thought I would ask here. Up until now, when designing a database, I always plotted out the relationships on paper, with pen/pencil. I've filled large pieces of paper with all the tables and their relationships. However, now I will be doing one of these large projects, and the client wants to see how I propose to do all the relationships. I would like to have something more professional than a 24x36' piece of paper with my scribblings all over it. This has been quite good enough for me, although it is decidedly low-tech. I've seen commercial applications that allow you to do this sort of thing, but I do not have much of a budget. I am wondering if anyone can direct me to a free program that allows me to do this sort of thing? I would prefer some sort of client side application that will allow me to click and create new tables, relationships, and so forth. It does not need to actually *do* anything, i.e., it does not need to create or execute SQL queries. I just want to model the relationships. Suggestions? What do other people use to model their database? Thanks for any input! Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Graphical program to describe table relationships
Hi Joshua, Hi All, This is more of a general SQL database question than a MySQL question, but since I use MySQL almost exclusively I thought I would ask here. Up until now, when designing a database, I always plotted out the relationships on paper, with pen/pencil. I've filled large pieces of paper with all the tables and their relationships. However, now I will be doing one of these large projects, and the client wants to see how I propose to do all the relationships. I would like to have something more professional than a 24x36' piece of paper with my scribblings all over it. This has been quite good enough for me, although it is decidedly low-tech. I've seen commercial applications that allow you to do this sort of thing, but I do not have much of a budget. I am wondering if anyone can direct me to a free program that allows me to do this sort of thing? I would prefer some sort of client side application that will allow me to click and create new tables, relationships, and so forth. It does not need to actually *do* anything, i.e., it does not need to create or execute SQL queries. I just want to model the relationships. Suggestions? What do other people use to model their database? Hi, I use DbDesigner http://www.fabforce.net/dbdesigner4/. It has some bugs but at all it's a great graphical designing tool. And it's free. -- Use the force - read the source Piotr Duszynski mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RI enforcement and m2m relationships.
I don't see how you can possibly hope to satisfy two mutually dependent constraints at the exact same time. It's a chicken-and-egg scenario. If you can't create your vacancy record first (to get its PK value) you won't be able to create the new record in your supervision table. Most people solve this dependency dilemma with either application-layer processing or by creating a stored procedure (new to MySQL 5.x). Many people also wrap the entire process in a transaction (supported in InnoDB) so that if you somehow fail to create the new supervision record then you would be able to rollback the transaction (un-creating the vacancy record), leaving your database in a consistent state (no vacancy would exist unless it has a corresponding supervision record). It's not hard to do what you want, but it requires logic external to the storage engine itself to enforce that kind of relationship. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine eifion herbert (IAH-C) To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: bsrc.ac.uk Fax to: Subject: RI enforcement and m2m relationships. 06/02/2004 06:45 AM Hi all, I'm developing a database to store job adverts to appear on a company website. Each vacancy has at least one supervisor, but may have more than one, and a supervisor may be responsible for multiple vacancies. I've resolved the vacancy--supervisor many-to-many relationship in the normal(?) way of having a table called supervision inbetween them with two columns, the Ref of the vacancy and the ID of the supervisor. Both these columns make up the pri key in the supervision table, and are also foreign keys referencing the pri keys in the vacancy and supervisor table. Unfortunately this allows a vacancy to be added to the database without a supervisor being assigned. Is there a way in mySQL4 or Perl to enforce this without creating an insertion anomaly? Cheers Eifion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RI enforcement and m2m relationships.
[EMAIL PROTECTED] wrote: I don't see how you can possibly hope to satisfy two mutually dependent constraints at the exact same time. It's a chicken-and-egg scenario. If you can't create your vacancy record first (to get its PK value) you won't be able to create the new record in your supervision table. (..) It's not hard to do what you want, but it requires logic external to the storage engine itself to enforce that kind of relationship. In databases this is usually solved in the storage engine itself by deferring constraint checking to transaction commit. Unfortunately, MySQL doesn't offer deferred constraints (haven't seen them on the ToDo list either). Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RES: RI enforcement and m2m relationships.
Hello, Maybe a solution to not allow a vacancy to be added without a supervisor being assigned: 1. Create an atribute of the identification in the supervision table as Primary Key (e.g. id_supervision). 2. Create column id_supervision in vacancy table as not null. 3. Create index in vacancy table to column id_supervision. 4. Create foreign key referencing the column id_supervision in vacancy table and supervision table. I hope that helps. Regards, Renato Cramer. -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 2 de junho de 2004 10:02 Para: eifion herbert (IAH-C) Cc: [EMAIL PROTECTED] Assunto: RI enforcement and m2m relationships. Hi all, I'm developing a database to store job adverts to appear on a company website. Each vacancy has at least one supervisor, but may have more than one, and a supervisor may be responsible for multiple vacancies. I've resolved the vacancy--supervisor many-to-many relationship in the normal(?) way of having a table called supervision inbetween them with two columns, the Ref of the vacancy and the ID of the supervisor. Both these columns make up the pri key in the supervision table, and are also foreign keys referencing the pri keys in the vacancy and supervisor table. Unfortunately this allows a vacancy to be added to the database without a supervisor being assigned. Is there a way in mySQL4 or Perl to enforce this without creating an insertion anomaly? Cheers Eifion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relationships Question
Dear List, I am working on a database of electronic compositions (and installations etc.) and currently have, amongst others, the following tables: ARTIST(_id_,...) SCORE(_id_,_artist_id_,...) COMPOSITIONS(_id_,_score_id_,...) INSTALLATIONS(_id_,_score_id_,...) PERFORMANCES(_id_,???) The PERFORMANCES table lists dates and locations (etc.) of performances of COMPOSITIONS and INSTALLATIONS in the database. My current entity relationship diagram gives a one-to-many relationship between COMPOSITIONS and PERFORMANCES and between INSTALLATIONS and PERFORMANCES. However, this would involve PERFORMANCES having two foriegn keys (composition_id and installation_id) one of which would be redundant. Whats the best way to design this relationship? Cheers, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relationships Question
Hi Richard, Can you make a relationship between COMPOSITIONS and INSTALATIONS? If so, then you make a relationship between COMPSOTIONS_ONSTALATIONS and PERFORMANCES. Laercio. -Original Message- From: Richard Lewis [mailto:[EMAIL PROTECTED] On Behalf Of Richard Lewis Sent: terça-feira, 25 de maio de 2004 09:58 To: [EMAIL PROTECTED] Subject: Relationships Question Dear List, I am working on a database of electronic compositions (and installations etc.) and currently have, amongst others, the following tables: ARTIST(_id_,...) SCORE(_id_,_artist_id_,...) COMPOSITIONS(_id_,_score_id_,...) INSTALLATIONS(_id_,_score_id_,...) PERFORMANCES(_id_,???) The PERFORMANCES table lists dates and locations (etc.) of performances of COMPOSITIONS and INSTALLATIONS in the database. My current entity relationship diagram gives a one-to-many relationship between COMPOSITIONS and PERFORMANCES and between INSTALLATIONS and PERFORMANCES. However, this would involve PERFORMANCES having two foriegn keys (composition_id and installation_id) one of which would be redundant. Whats the best way to design this relationship? Cheers, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search for relationships that aren't present
Three tables: computers (Describes computers) users (Describes users) comp-user-link (Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search for relationships that aren't present
If you are using a version that supports sub-selects you could perform a subquery. Otherwise,the solution depends on your primary keys. SELECT u.* FROM users u LEFT OUTER JOIN comp-user-link c ON u.userID = c.userID WHERE c.userID IS NULL -Original Message- From: Brad Tilley To: [EMAIL PROTECTED] Sent: 5/25/04 1:39 PM Subject: Search for relationships that aren't present Three tables: computers (Describes computers) users (Describes users) comp-user-link (Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
Brad, This is also a design issue. Generally when designing tables to support a one-to-many relationship, you put a pointer field in the many table and load it with the ID value of the one that it relates to. In this case you would want a nullable field like users_id on your computers table. Since there is only 1 blank to assign a computer to you get only 2 states, the computer is assigned to someone or it isn't (it has a value or is null). Link tables, like you have, are generally only created for a many-to-many relationship. If several printers could all be used by multiple computers, that would be an example of a many-to-many relationship. In either case, testing for the nonexistence of one side of a relationship can be accomplished through a LEFT JOIN. To use your link table to test to see if any user's are not in the link table you could write: SELECT users.ID, users.Name FROM users LEFT JOIN comp-user-link ON comp-user-link.user_id = users.user_id WHERE comp-user-link is NULL If you redesign your relationship to eliminate the link table, you would change this query by replacing all instances of comp-user-link with computers to get the same results (users not assigned computers). Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Brad Tilley [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: 05/25/2004 02:39 Fax to: PM Subject: Search for relationships that aren't present Three tables: computers (Describes computers) users(Describes users) comp-user-link (Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
I should better describe the tables: computers has 'comp_id' as its primary, unique key. users has 'user_id' as its primary, unique key. comp-user-link has two (and only two fields) that are *never* null: 'comp_id' (which must be unique), and 'user_id' Every computer is linked to a user... the problem is some users (those who have left, or returned computers for various reasons) are still in our users table for historical purposes... we maintain a 'transfers' table to show when computers were given to users... this is why we never throw users away even after they're no longer associated with us. Does that make sense? Anyway, I only want to show the users that currently are not linked to a computer and the linking table does not contain nulls. Thanks. Victor Pendleton wrote: If you are using a version that supports sub-selects you could perform a subquery. Otherwise,the solution depends on your primary keys. SELECT u.* FROM users u LEFT OUTER JOIN comp-user-link c ON u.userID = c.userID WHERE c.userID IS NULL -Original Message- From: Brad Tilley To: [EMAIL PROTECTED] Sent: 5/25/04 1:39 PM Subject: Search for relationships that aren't present Three tables: computers (Describes computers) users (Describes users) comp-user-link (Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
Brad Tilley wrote: I should better describe the tables: computers has 'comp_id' as its primary, unique key. users has 'user_id' as its primary, unique key. comp-user-link has two (and only two fields) that are *never* null: Except within the context of a left join. Every user in a left join matches up with at least one record in comp-user-link. If the record is not real, it consists of all NULLs 'comp_id' (which must be unique), and 'user_id' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
Victor Shawn, Thanks for the select info and the relationship info. Both of your examples worked. The reason we designed a separate linking table is that we wanted to always keep the computers separate from the users. A container of users and a container of computers with a linking table joining them. Yes, it's a bit more complex, but it works well for us... especially with the amount of transfers that we do between users. Thanks again, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
Ah yes, thank you Gerald. I didn't understand this until now. I guess I should say that 'by design' the linking table will never contain a null, but the left join changes that. Thank you for pointing that out. gerald_clark wrote: Brad Tilley wrote: I should better describe the tables: computers has 'comp_id' as its primary, unique key. users has 'user_id' as its primary, unique key. comp-user-link has two (and only two fields) that are *never* null: Except within the context of a left join. Every user in a left join matches up with at least one record in comp-user-link. If the record is not real, it consists of all NULLs 'comp_id' (which must be unique), and 'user_id' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
So you do have two ways to associate users with computers. One is directly on the computers table and the other is through the link table. That leaves me with two questions to answer: Question 1) Are there any users that do not have an ID in the user_id field on the computers table? Question 2) Are there any users that no not have an ID in the user_id field of the comp-user-link table? It's the function of the LEFT JOIN clause to return all rows from one table (the one on the LEFT of the statement) and only those rows from the other table where the ON clause is satisfied. For all rows where the ON clause is NOT satisfied, the query engine NULLs-out all of the columns in the other table. So to answer Q1: SELECT users.user_id FROM users LEFT JOIN computers ON computers.user_id = users.user_id where computers.user_id is NULL This works because if there is a row in the users table that will not line up with a row in the computers table, all values that would have normally been provided by the actual data from the computers table will return as NULL. This is independent of the actual table definition. To answer Q2: SELECT users.user_id FROM users LEFT JOIN comp-user-link ON comp-user-link.user_id = users.user_id where comp-user-link.user_id is NULL The LEFT JOIN fills in the columns of the comp-user-link table on the non-aligned rows (where there exists a user but no link) with the null values for you. Respecfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Brad Tilley [EMAIL PROTECTED] To: Victor Pendleton [EMAIL PROTECTED] cc: '[EMAIL PROTECTED] ' [EMAIL PROTECTED] 05/25/2004 02:58 Fax to: PM Subject: Re: Search for relationships that aren't present I should better describe the tables: computers has 'comp_id' as its primary, unique key. users has 'user_id' as its primary, unique key. comp-user-link has two (and only two fields) that are *never* null: 'comp_id' (which must be unique), and 'user_id' Every computer is linked to a user... the problem is some users (those who have left, or returned computers for various reasons) are still in our users table for historical purposes... we maintain a 'transfers' table to show when computers were given to users... this is why we never throw users away even after they're no longer associated with us. Does that make sense? Anyway, I only want to show the users that currently are not linked to a computer and the linking table does not contain nulls. Thanks. Victor Pendleton wrote: If you are using a version that supports sub-selects you could perform a subquery. Otherwise,the solution depends on your primary keys. SELECT u.* FROM users u LEFT OUTER JOIN comp-user-link c ON u.userID = c.userID WHERE c.userID IS NULL -Original Message- From: Brad Tilley To: [EMAIL PROTECTED] Sent: 5/25/04 1:39 PM Subject: Search for relationships that aren't present Three tables: computers(Describes computers) users(Describes users) comp-user-link (Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
Brad Tilley wrote: I should better describe the tables: computers has 'comp_id' as its primary, unique key. users has 'user_id' as its primary, unique key. comp-user-link has two (and only two fields) that are *never* null: 'comp_id' (which must be unique), and 'user_id' Every computer is linked to a user... the problem is some users (those who have left, or returned computers for various reasons) are still in our users table for historical purposes... we maintain a 'transfers' table to show when computers were given to users... this is why we never throw users away even after they're no longer associated with us. Does that make sense? Anyway, I only want to show the users that currently are not linked to a computer and the linking table does not contain nulls. Thanks. Then you need to use the query that Victor Pendleton and Shawn Green already sent you: SELECT users.ID, users.Name FROM users LEFT JOIN comp-user-link ON comp-user-link.user_id = users.user_id WHERE comp-user-link.user_id IS NULL The LEFT JOIN gives you a result for every row in the first (left) table, regardless of whether or not it has a match in the second (right) table. In the case of a row with no match in the right table, you get NULLs for the right table columns. So, users who are in the users table but not the comp-user-link table will be returned by the LEFT JOIN with NULLs in the comp-user-link columns, and the WHERE clause says to only show those rows. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for relationships that aren't present
a LEFT JOIN should do the trick something like this: SELECT users.user_id FROM users LEFT JOIN comps_users_link ON (users.user_id=comps_users_link.user_id) WHERE comps_users_link.computer_id IS NULL olinux --- Brad Tilley wrote: Three tables: computers (Describes computers) users (Describes users) comp-user-link(Links users to computers in a 1 user to many computers relationship) Could someone offer advice on how to construct a select statement to show which users aren't in the comp-user-link table? Sort of the reverse of this: select * from comp-user-link, users where users.user_id = comps_users_link.user_id Basically, I'm trying to see which users aren't currently linked to a computer. As always, Thanks! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modelling specialized M:N relationships?
I'm actually i deep doubts with this. Price is not a thing itself, good point. But in my case, it was the best way i found out to model. Suppose you got a company the rents a place (just a big room, or a party house) for any kinda envents you'd to make. The company is clear an entity, and the price would probably be its attribut, despite the following problem: This company charges diferent prices depending on the kind of party you'd like to make on its party House. You might have a company that charges you the following. - Wedding party U$100 - Under Graduation party (prom) U$200 - high school grad party (hs prom) U$ 300 Beyond that, there are the ordinary companies that have only one basic price for any kind of party (they don't change their prices depending on the parties). That's why i decided to model it as an entity, but i'm not sure in two aspects. - Should i have a M:N or a 1:N relation between these entities (note that this is actually not that trivial, maybe modelled both ways) - Should i just have a column in my price entity to specify the kinda party (like PARTY_TYPE) or just have 3 or more specific columns for the party type (lik PRICE_WEDDING, PRICE_GRADUATION, PRICE_HIGH_SCHOOL). Well, that's it ... If u could give an advise... Thanks in advance, ltcmelo --- Robert J Taylor [EMAIL PROTECTED] escreveu: Leandro Melo wrote: Hi, i have a M:N relationship between PRODUCT and PRICE. Is Price an Entity or an Attribute? I'm not accustomed to using price as a Thing. What is it that causes a Product to have one, exactly, or more prices? Is it its relationship to some other thing? Or is it a special kind of Product? Except during a couple years under US President Nixon, Prices are usually not things in themselves... can you flesh out what you are trying to model a little more? 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Let's see what relationship to a another entity or classifying attribute of Product might invoke the rule on one-and-only-one or M:N. Then we'll know how better to proceed. Thanks, ltcmelo __ HTH, Robert Taylor [EMAIL PROTECTED] begin:vcard fn:Robert Taylor n:Taylor;Robert adr;dom:;;9 Via Latigo;Rancho Santa Margarita;CA;92688 email;internet:[EMAIL PROTECTED] tel;work:949 677 0321 tel;cell:949 677 0321 x-mozilla-html:FALSE url:http://rjamestaylor.com version:2.1 end:vcard __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Relationships
OK I am a umber newbie. And I have decided to change that. I was wondering how do you create relationships between tables? If you know where it talks about it in the MySQL reference manual you can direct me there. But I have not been able to find it. So this is my first question. Thank you! Bryan Goodwin
RE: Creating Relationships
Use INNODB and use FK constraints / cascades. -Original Message- From: LastingImages [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 6:33 PM To: [EMAIL PROTECTED] Subject: Creating Relationships OK I am a umber newbie. And I have decided to change that. I was wondering how do you create relationships between tables? If you know where it talks about it in the MySQL reference manual you can direct me there. But I have not been able to find it. So this is my first question. Thank you! Bryan Goodwin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Modelling specialized M:N relationships?
Hi, i have a M:N relationship between PRODUCT and PRICE. 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Thanks, ltcmelo __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modelling specialized M:N relationships?
Leandro Melo wrote: Hi, i have a M:N relationship between PRODUCT and PRICE. Is Price an Entity or an Attribute? I'm not accustomed to using price as a Thing. What is it that causes a Product to have one, exactly, or more prices? Is it its relationship to some other thing? Or is it a special kind of Product? Except during a couple years under US President Nixon, Prices are usually not things in themselves... can you flesh out what you are trying to model a little more? 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Let's see what relationship to a another entity or classifying attribute of Product might invoke the rule on one-and-only-one or M:N. Then we'll know how better to proceed. Thanks, ltcmelo __ HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relationships
How do I create relationships between tables? I have tried but have no way of being sure that I am successful just by looking at the db. I am not sure how to relate one record in one table to it corresponding record in another. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relationships
Hi, is there a way of showing relationships in mysql? such as. mysql SHOW relationships Bob
Re: relationships
* Robert Morgan Hi, is there a way of showing relationships in mysql? such as. mysql SHOW relationships No. The relationships are dynamic, defined by join statements on the fly. You can however show any defined foreign key constraints using SHOW CREATE TABLE tablename. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: relationships
What do you mean by relations? Like table relations such as with FK in innodb? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Robert Morgan [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:18 AM --To: mysqllist --Subject: relationships -- --Hi, is there a way of showing relationships in mysql? -- --such as. mysql SHOW relationships -- --Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relationships - Foreign Key
Hi, sorry for my english, but, how can I do relationships between tables? Is possible? Thanks, Paulo Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relationships - Foreign Key
* Paulo Hi, sorry for my english, but, how can I do relationships between tables? Is possible? This is done using different types of JOIN: URL: http://www.mysql.com/doc/en/JOIN.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using joins to create code from E- ER relationships
Hello, starting from some software database spesification, defined in some Enhanced entity relationship diagram, resulting in all kinds of relationships, 1:1 1:Many Many:1 Many : many And last but not least: Subclass/superclass relationship This seems to be a small problem for me, With the current version of MySQL. How can I use joins to code in SQL syntax an arbitrary model of An Entity relationship diagram ? With all constraints correctly implemented, Without any foreign keys ? Without any on delete set default, I hope this can be implemented also without Combining MySQL to PHP, Perl or C. Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relationships in Mysql
el [DATE], [NAME] en [ADDRESS] escribió: Thank you for your help and for the lesson. Eli I would just add a few words to Nils' excellent reply: In the most general sense, a foreign key is just a primary key value that occurs outside of its native table (i.e. in a foreign table, get it?) in order to point to the record in its native table with that value as its primary key. You can use foreign keys in your database design and programming without actually formally declaring them as such in the database system. Indeed, as you point out, you *have* to use them to establish relationships among your tables. If the database system doesn't recognize the foreign key columns as foreign keys, then you must programatically ensure that referential integrity is maintained: --when you insert a new record, make sure that any foreign key points to an existing record (a record with that key value as its primary key) --don't ever change the value of a record's primary key (that is, unless you are prepared to change the value of all the foreign keys in all the records pointing to that record) --don't ever delete a record that has other records pointing to it via foreign keys (unless you delete all those records too). Many people assume that a relational database system *must* recognize such foreign keys (and relationships among the tables) in order to be truly relational. But the term relational refers, not to the relationships among the tables, but to the basic relation that is defined by each table (and Codd's theory that even the most complex data structure can be broken down into such simple relations). --John On Thursday 24 July 2003 08:00 pm, Nils Valentin wrote: Hi Eli, The short answer is .. you create relationships between tables by creating foreign keys and primary keys. However, you might not even need them ;-) - I explain below. Foreign keys and primary keys are used to create relations between tables. Using them will bind you to some rules which you can use to your advantage (or not), thats entirely up to you. If you define a primary key than any foreign key can link to the primary key. However , a foreign ke can only contain a value which is already defined in a primary key. This can be used to restrict that only valid values are insert for foreign keys or f.e no primary key is deleted where still existing foreign keys are pointing to the primary key record. Currently only the InnoDB table format supports Foreign keys. In order to use a join you dont need to create any keys. You can free define the columns to be used for the join condition. Please see the JOIN Syntax for more information. http://www.mysql.com/doc/en/JOIN.html Best regards Nils Valentin Tokyo/Japan 2003? 7? 25? ??? 03:36?eli : Hi, Can anyone explain me how to create relationships between tables in Mysql? For making some kind of functions like joins, it is indispensable to have relationships defined? Thanks in advanced. eli SetFile DATABASE FACTORY - Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh ) [EMAIL PROTECTED]www.setfile.com/esTEL 93 238 56 00 FileMaker Inc es miembro del BSA ( 900 211 048) SetFile - FSA Partners -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Relationships in Mysql
I am a newbie to db.
I have a few tables, one that I define the primary key.
CREATE TABLE member (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL AUTO_INCREMENT,
last VARCHAR(20) DEFAULT '' NOT NULL,
first VARCHAR(20) DEFAULT '' NOT NULL,
mi CHAR(1) DEFAULT '' NOT NULL,
PRIMARY KEY(memid),
INDEX name (last,first,mi)) TYPE=INNODB;
CREATE TABLE memadv (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL,
advtypeENUM('A','T','C')DEFAULT 'C' NOT NULL,
FOREIGN KEY (memid) REFERENCES member(memid)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX id (memid,advtype)) TYPE=INNODB;
What I want is for the primary key to be auto generated and propagated
to the other table's memid automatically, when data is entered for
the member table? Does that make sense? Am I doint this correctly?
Thanks for the help,
Carlos
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Relationships in Mysql
Hi Carlos,
I believe that would have to be 2 separated steps.
1) creating the value in the primary key and
2) creating the foreign key value
However you can use UNION to write both statements in one go.
Best regards
Nils Valentin
Tokyo/Japan
2003 7 26 05:20Write a Friend :
I am a newbie to db.
I have a few tables, one that I define the primary key.
CREATE TABLE member (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL
AUTO_INCREMENT, last VARCHAR(20) DEFAULT '' NOT
NULL, first VARCHAR(20) DEFAULT '' NOT NULL, mi
CHAR(1) DEFAULT '' NOT NULL, PRIMARY KEY(memid),
INDEX name (last,first,mi)) TYPE=INNODB;
CREATE TABLE memadv (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL,
advtypeENUM('A','T','C')DEFAULT 'C' NOT NULL,
FOREIGN KEY (memid) REFERENCES member(memid)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX id (memid,advtype)) TYPE=INNODB;
What I want is for the primary key to be auto generated and propagated
to the other table's memid automatically, when data is entered for
the member table? Does that make sense? Am I doint this correctly?
Thanks for the help,
Carlos
--
---
Valentin Nils
Internet Technology
E-Mail: [EMAIL PROTECTED]
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Relationships in Mysql
Nils,
Thanks. I will code accordingly.
Carlos
Hi Carlos,
I believe that would have to be 2 separated steps.
1) creating the value in the primary key and
2) creating the foreign key value
However you can use UNION to write both statements in one go.
Best regards
Nils Valentin
Tokyo/Japan
2003年 7月 26日 土曜日 05:20、Write a Friend さんは書きました:
I am a newbie to db.
I have a few tables, one that I define the primary key.
CREATE TABLE member (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL
AUTO_INCREMENT, last VARCHAR(20) DEFAULT '' NOT
NULL, first VARCHAR(20) DEFAULT '' NOT NULL, mi
CHAR(1) DEFAULT '' NOT NULL, PRIMARY KEY(memid),
INDEX name (last,first,mi)) TYPE=INNODB;
CREATE TABLE memadv (
memid INT(6) UNSIGNED ZEROFILL DEFAULT '002000' NOT NULL,
advtypeENUM('A','T','C')DEFAULT 'C' NOT NULL,
FOREIGN KEY (memid) REFERENCES member(memid)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX id (memid,advtype)) TYPE=INNODB;
What I want is for the primary key to be auto generated and propagated
to the other table's memid automatically, when data is entered for
the member table? Does that make sense? Am I doint this correctly?
Thanks for the help,
Carlos
--
---
Valentin Nils
Internet Technology
E-Mail: [EMAIL PROTECTED]
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Relationships in Mysql
Hi, Can anyone explain me how to create relationships between tables in Mysql? For making some kind of functions like joins, it is indispensable to have relationships defined? Thanks in advanced. eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Relationships in Mysql
Hi Eli, The short answer is .. you create relationships between tables by creating foreign keys and primary keys. However, you might not even need them ;-) - I explain below. Foreign keys and primary keys are used to create relations between tables. Using them will bind you to some rules which you can use to your advantage (or not), thats entirely up to you. If you define a primary key than any foreign key can link to the primary key. However , a foreign ke can only contain a value which is already defined in a primary key. This can be used to restrict that only valid values are insert for foreign keys or f.e no primary key is deleted where still existing foreign keys are pointing to the primary key record. Currently only the InnoDB table format supports Foreign keys. In order to use a join you dont need to create any keys. You can free define the columns to be used for the join condition. Please see the JOIN Syntax for more information. http://www.mysql.com/doc/en/JOIN.html Best regards Nils Valentin Tokyo/Japan 2003 7 25 03:36eli : Hi, Can anyone explain me how to create relationships between tables in Mysql? For making some kind of functions like joins, it is indispensable to have relationships defined? Thanks in advanced. eli -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Relationships in Mysql
I would just add a few words to Nils' excellent reply: In the most general sense, a foreign key is just a primary key value that occurs outside of its native table (i.e. in a foreign table, get it?) in order to point to the record in its native table with that value as its primary key. You can use foreign keys in your database design and programming without actually formally declaring them as such in the database system. Indeed, as you point out, you *have* to use them to establish relationships among your tables. If the database system doesn't recognize the foreign key columns as foreign keys, then you must programatically ensure that referential integrity is maintained: --when you insert a new record, make sure that any foreign key points to an existing record (a record with that key value as its primary key) --don't ever change the value of a record's primary key (that is, unless you are prepared to change the value of all the foreign keys in all the records pointing to that record) --don't ever delete a record that has other records pointing to it via foreign keys (unless you delete all those records too). Many people assume that a relational database system *must* recognize such foreign keys (and relationships among the tables) in order to be truly relational. But the term relational refers, not to the relationships among the tables, but to the basic relation that is defined by each table (and Codd's theory that even the most complex data structure can be broken down into such simple relations). --John On Thursday 24 July 2003 08:00 pm, Nils Valentin wrote: Hi Eli, The short answer is .. you create relationships between tables by creating foreign keys and primary keys. However, you might not even need them ;-) - I explain below. Foreign keys and primary keys are used to create relations between tables. Using them will bind you to some rules which you can use to your advantage (or not), thats entirely up to you. If you define a primary key than any foreign key can link to the primary key. However , a foreign ke can only contain a value which is already defined in a primary key. This can be used to restrict that only valid values are insert for foreign keys or f.e no primary key is deleted where still existing foreign keys are pointing to the primary key record. Currently only the InnoDB table format supports Foreign keys. In order to use a join you dont need to create any keys. You can free define the columns to be used for the join condition. Please see the JOIN Syntax for more information. http://www.mysql.com/doc/en/JOIN.html Best regards Nils Valentin Tokyo/Japan 2003 7 25 03:36eli : Hi, Can anyone explain me how to create relationships between tables in Mysql? For making some kind of functions like joins, it is indispensable to have relationships defined? Thanks in advanced. eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Need Help Setting Relationships
On Friday 07 March 2003 00:43, Chris Montgomery wrote: I have been using MS Access for 5+ years and am new to MySQL. I understand the theory behind setting relationships between tables, but am trying to get up to speed in how to do it in MySQL. My environment: Win2k and MySQL 3.23.54 Here's what I want to do: I have two tables, categories and businesses, and I want to create a relationship between them based on the Node field (primary key in the categories table, foreign key in the businesses table). I guess what I fully don't understand yet is the order to do this: create indexes on the key/foreign key fields and then create foreign key constraints or vice versa? [skip] I've tried using a couple of GUI front-ends to manage this but keep getting errors when setting up the foreign key relationships. Can someone please point me in the right direction? Many thanks in advance. Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Need Help Setting Relationships
Howdy Egor, Friday, March 7, 2003, 7:36:00 AM, Egor Egorov wrote: Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). TIA. -- Chris Montgomery - 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: Need Help Setting Relationships
Disregard my previous msg. It looks like I have it figured out. Friday, March 7, 2003, 5:31:38 PM, Chris Montgomery wrote: I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). Cheers. -- Chris Montgomery - 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
Need Help Setting Relationships
Howdy, I have been using MS Access for 5+ years and am new to MySQL. I understand the theory behind setting relationships between tables, but am trying to get up to speed in how to do it in MySQL. My environment: Win2k and MySQL 3.23.54 Here's what I want to do: I have two tables, categories and businesses, and I want to create a relationship between them based on the Node field (primary key in the categories table, foreign key in the businesses table). I guess what I fully don't understand yet is the order to do this: create indexes on the key/foreign key fields and then create foreign key constraints or vice versa? Here's a dump of my tables: Column Information For - saopinions.categories Field Type NullKey Default Extra -- -- -- --- -- Nodesmallint(5) unsigned PRI (NULL) auto_increment ParentNode smallint(5) unsigned 0 Lineage varchar(100) YES Categoryvarchar(100) Depth tinyint(4) unsigned 0 Index Information For - saopinions.categories Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment -- -- --- - --- -- --- categories 0 PRIMARY 1 Node A 62 (NULL)(NULL) DDL Information For - saopinions.categories Table Create Table -- - categories CREATE TABLE `categories` ( `Node` smallint(5) unsigned NOT NULL auto_increment, `ParentNode` smallint(5) unsigned NOT NULL default '0', `Lineage` varchar(100) default '', `Category` varchar(100) NOT NULL default '', `Depth` tinyint(4) unsigned NOT NULL default '0
Re: updating many-to-many relationships?
I don't have a mailing list, and I don't appreciate your email. You subscribed to the mysql mailing list, or you would not be getting it. The instructions to unsubscribe are at the bottom of every message. Kip McGee wrote: will you please take me off of your mailing list if you dont im gonna send my knee breaker QUEDO SARDUCHIE Please do not make me send Quedo he is a ornery ass whipping midgit. I have been getting over a hundred e mails every day from your company and i cannot even use my computer becouse it is so full caca!! I must be on your mailing list and i wish to be taken off of it immediatly. my email address is [EMAIL PROTECTED] - 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
updating many-to-many relationships?
Hi all. I need some help figuring out how to update a many-to-many relationship from a text file. For the fun of it, let's call the tables 'sku' and 'agent'. Each sku can have multiple agents (usually 10, always 0), and each agent can be associated with an unlimited number of records skus. Let's say, for example, we're using: CREATE TABLE sku ( id int unsigned NOT NULL auto_increment, sku varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY sku (sku), ); CREATE TABLE agent ( id mediumint unsigned NOT NULL auto_increment, agent varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY agent (agent), ); CREATE TABLE skuagent ( sku int(10) unsigned NOT NULL, agent mediumint(5) unsigned NOT NULL, UNIQUE KEY skuagent (sku,agent), KEY agentsku (agent,sku), ); That's fine as far as it goes, but I can't figure out how to repopulate the tables when new data comes in. On a regular basis (probably once every two days), I'll be getting a new text file with the relationships in a format that looks like: SKU_ONE AGENT_ONE SKU_ONE AGENT_TWO SKU_TWO AGENT_ONE SKU_TWO AGENT_TWO SKU_TWO AGENT_THREE SKU_THREE AGENT_BLUE SKU_THREE AGENT_ORANGE etc. The text is what gets shoved into the varchar columns. The input text file does indeed have SKUs grouped as shown, so it's easy to `uniq`. The problem is that with each update, I'll be getting a different set of relationships on perhaps 3% of the SKUs, with some new SKUs and AGENTs, and some removed. I can add the new records easily enough, and orphan records aren't a problem. What I can't figure out is a good way to do the many-to-many update. I can do it by emptying the skuagent table every time I get a new file, then re-populating it from scratch by running a new INSERT...SELECT for each line in the file. But the text file has over forty million lines, with 15 million unique SKUs and half a million AGENTs. I really don't want to take the database down for hours at a time just to refresh. Or alternately, a grottier solution. Each time I get a new file, I can run a script that will go through each unique SKU in the file, delete the skuagent records for THAT SKU ONLY, and re-add them with current data. It's *way* more processing time for every host in the loop, but at least I won't have to take the whole thing down. I just can't seem to figure out an elegant solution. Is there one, or must I do this the ugly way? Any advice would be appreciated. :) p (And for the list robot: sql,query,queries,smallint) -- Paul Chvostek [EMAIL PROTECTED] Operations / Abuse / Whatever it.canada, hosting and development http://www.it.ca/ - 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: updating many-to-many relationships?
Looks to my like skuagent should be using the varchar fields instead of the int fields. Then you could just insert ignore each line from your test file. Paul Chvostek wrote: Hi all. I need some help figuring out how to update a many-to-many relationship from a text file. For the fun of it, let's call the tables 'sku' and 'agent'. Each sku can have multiple agents (usually 10, always 0), and each agent can be associated with an unlimited number of records skus. Let's say, for example, we're using: CREATE TABLE sku ( id int unsigned NOT NULL auto_increment, sku varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY sku (sku), ); CREATE TABLE agent ( id mediumint unsigned NOT NULL auto_increment, agent varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY agent (agent), ); CREATE TABLE skuagent ( sku int(10) unsigned NOT NULL, agent mediumint(5) unsigned NOT NULL, UNIQUE KEY skuagent (sku,agent), KEY agentsku (agent,sku), ); That's fine as far as it goes, but I can't figure out how to repopulate the tables when new data comes in. On a regular basis (probably once every two days), I'll be getting a new text file with the relationships in a format that looks like: SKU_ONE AGENT_ONE SKU_ONE AGENT_TWO SKU_TWO AGENT_ONE SKU_TWO AGENT_TWO SKU_TWO AGENT_THREE SKU_THREE AGENT_BLUE SKU_THREE AGENT_ORANGE etc. The text is what gets shoved into the varchar columns. The input text file does indeed have SKUs grouped as shown, so it's easy to `uniq`. The problem is that with each update, I'll be getting a different set of relationships on perhaps 3% of the SKUs, with some new SKUs and AGENTs, and some removed. I can add the new records easily enough, and orphan records aren't a problem. What I can't figure out is a good way to do the many-to-many update. I can do it by emptying the skuagent table every time I get a new file, then re-populating it from scratch by running a new INSERT...SELECT for each line in the file. But the text file has over forty million lines, with 15 million unique SKUs and half a million AGENTs. I really don't want to take the database down for hours at a time just to refresh. Or alternately, a grottier solution. Each time I get a new file, I can run a script that will go through each unique SKU in the file, delete the skuagent records for THAT SKU ONLY, and re-add them with current data. It's *way* more processing time for every host in the loop, but at least I won't have to take the whole thing down. I just can't seem to figure out an elegant solution. Is there one, or must I do this the ugly way? Any advice would be appreciated. :) p (And for the list robot: sql,query,queries,smallint) - 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: updating many-to-many relationships?
I *really* don't want to have a pair of indexes on 60-character varchars that cover forty million records. That would increase the size of each index from 280MB to 4.8GB. Just for the indexes. I don't think so. Also, I don't see how it would address the issue of updates. The problem is that for each sku, the list of agents is *changing*. Some will be added, some will be removed. I'm already doing an insert ignore to top up the sku and agent tables, so populating the skuagent table with new entries consists of: INSERT INTO skuagent (sku,agent) SELECT sku.id,agent.id FROM sku,agent WHERE sku.sku='%s' AND agent.agent='%s'; for each line in the input text file. But the more I think about this, the more it looks as if I'm going to have to process things the slow and ugly way Thanks anyway for looking at this. p On Wed, Feb 19, 2003 at 08:13:12AM -0600, gerald_clark wrote: Looks to my like skuagent should be using the varchar fields instead of the int fields. Then you could just insert ignore each line from your test file. Paul Chvostek wrote: Hi all. I need some help figuring out how to update a many-to-many relationship from a text file. For the fun of it, let's call the tables 'sku' and 'agent'. Each sku can have multiple agents (usually 10, always 0), and each agent can be associated with an unlimited number of records skus. Let's say, for example, we're using: CREATE TABLE sku ( id int unsigned NOT NULL auto_increment, sku varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY sku (sku), ); CREATE TABLE agent ( id mediumint unsigned NOT NULL auto_increment, agent varchar(60) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY agent (agent), ); CREATE TABLE skuagent ( sku int(10) unsigned NOT NULL, agent mediumint(5) unsigned NOT NULL, UNIQUE KEY skuagent (sku,agent), KEY agentsku (agent,sku), ); That's fine as far as it goes, but I can't figure out how to repopulate the tables when new data comes in. On a regular basis (probably once every two days), I'll be getting a new text file with the relationships in a format that looks like: SKU_ONE AGENT_ONE SKU_ONE AGENT_TWO SKU_TWO AGENT_ONE SKU_TWO AGENT_TWO SKU_TWO AGENT_THREE SKU_THREE AGENT_BLUE SKU_THREE AGENT_ORANGE etc. The text is what gets shoved into the varchar columns. The input text file does indeed have SKUs grouped as shown, so it's easy to `uniq`. The problem is that with each update, I'll be getting a different set of relationships on perhaps 3% of the SKUs, with some new SKUs and AGENTs, and some removed. I can add the new records easily enough, and orphan records aren't a problem. What I can't figure out is a good way to do the many-to-many update. I can do it by emptying the skuagent table every time I get a new file, then re-populating it from scratch by running a new INSERT...SELECT for each line in the file. But the text file has over forty million lines, with 15 million unique SKUs and half a million AGENTs. I really don't want to take the database down for hours at a time just to refresh. Or alternately, a grottier solution. Each time I get a new file, I can run a script that will go through each unique SKU in the file, delete the skuagent records for THAT SKU ONLY, and re-add them with current data. It's *way* more processing time for every host in the loop, but at least I won't have to take the whole thing down. I just can't seem to figure out an elegant solution. Is there one, or must I do this the ugly way? Any advice would be appreciated. :) p (And for the list robot: sql,query,queries,smallint) - 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 -- Paul Chvostek [EMAIL PROTECTED] Operations / Abuse / Whatever it.canada, hosting and development http://www.it.ca/ - 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
Whats the best way to manage 'generic two-way relationships'?
I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) My first thought was: CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX ); so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would need to do a lot of string parsing to get related stories, and I have to believe that a query with a 'LIKE %id%' clause is going to be a lot slower than one with an OR looking on two indexed rows. Any suggestions? - 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: Whats the best way to manage 'generic two-way relationships'?
Geoffrey, Friday, January 17, 2003, 12:01:20 PM, you wrote: I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Their is no easy answer.. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? This would depend on your business needs... if f relates to b.. do you assume c, d, e relations to f are equal to b's?? if so.. then inserting the extra rows could be valid. Or you could look at it as f relates to b.. b relates to c,d,eand NOT add the inserts then you could have Levels of relation (degrees of separation) and have a script that looks throught the heirarchy. You would have to watch for infinate looks as you could have an e relates to f row somewhere which would go on forever. It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) Having the extra inserts in the DB can be a pain.. BUT at runtime it will be faster as all the combinations are made already.. and you done need to parse the tree heirarchy of relations (f - b, b - c,d,e) but again.. all depends on how you want to treat things * extra rows: will speed up runtime usage.. but extra storage and inserts * parse heirarchy (recursive code): slower at runtime.. but you would not have to worry about maintaining the entire heirarchy in the db table think of it like this.. 9+ extra inserts when a story is saved.. OR a bunch of different SQL selects at runtime EVERY TIME the story gets views (if you were displaying related story info that is) // to get related articles without storing values select stroyid2 from storyRelationship where storyid1 = f then have to loop throught that resultset for each storyid2 to get what it relates to.. then loop through those,, and so on (recursively) Personally, i like the extra storage of the rows.. sql is easier (selects) and faster at runtime usually CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX ); an example: I would add a separation_level INT UNSIGNED NOT NULL INDEX to the table. This would allow you to query and get everything that relates to f.. Directly.. or at any separation level. Then you build all the relationships like And yes, to build it you would have to do some recursive calls possible.. but at least only once for each save (not for every view) in your example (f - b,k b - c,d,e , k - l,m) relid stroyid1 storyid2 separation_level 1 fb 0 2 bc 0 3 bd 0 4 be 0 5 fc 1 6 fd 1 7 fe 1 8 kl 0 9 km 0 10 fk 0 11 fl 2 12 fm 2 to get direct relations to f Select storyid2 from storyRelationship where storyid1 = f and separation_level = 0 or remove the separateion_level = 0 clause.. and it will return all related stories reguardless of how. I would use the separation_level to at least Rank how important a story is related to f.. just order by seperation_level so direct relations are first.. The only thing you loose by having all the compinations in the table.. is HOW the relation was made... no way to tell that the reason f relates to c,d,e was because b related to them... If you use the code heirarchy parser, you could maintain that kind of info.. but you probably done need it i think (depends on what your business needs are) Brian so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would need to do a lot of string parsing to get related stories, and I have to believe that a query with a 'LIKE %id%' clause is going to be a lot slower than one with an OR looking on two indexed rows. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php
Re[2]: Whats the best way to manage 'generic two-way relationships'?
oops small mistake in my prev post.. I meant to say (f - b b- c,d,e, Kk- l,m) in my example NOT f-b,k b-c,d,e lol.. got a little confused with all those insert examples ;) Friday, January 17, 2003, 2:01:22 PM, you wrote: Geoffrey, Friday, January 17, 2003, 12:01:20 PM, you wrote: I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Their is no easy answer.. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? This would depend on your business needs... if f relates to b.. do you assume c, d, e relations to f are equal to b's?? if so.. then inserting the extra rows could be valid. Or you could look at it as f relates to b.. b relates to c,d,eand NOT add the inserts then you could have Levels of relation (degrees of separation) and have a script that looks throught the heirarchy. You would have to watch for infinate looks as you could have an e relates to f row somewhere which would go on forever. It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) Having the extra inserts in the DB can be a pain.. BUT at runtime it will be faster as all the combinations are made already.. and you done need to parse the tree heirarchy of relations (f - b, b - c,d,e) but again.. all depends on how you want to treat things * extra rows: will speed up runtime usage.. but extra storage and inserts * parse heirarchy (recursive code): slower at runtime.. but you would not have to worry about maintaining the entire heirarchy in the db table think of it like this.. 9+ extra inserts when a story is saved.. OR a bunch of different SQL selects at runtime EVERY TIME the story gets views (if you were displaying related story info that is) // to get related articles without storing values select stroyid2 from storyRelationship where storyid1 = f then have to loop throught that resultset for each storyid2 to get what it relates to.. then loop through those,, and so on (recursively) Personally, i like the extra storage of the rows.. sql is easier (selects) and faster at runtime usually CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX ); an example: I would add a separation_level INT UNSIGNED NOT NULL INDEX to the table. This would allow you to query and get everything that relates to f.. Directly.. or at any separation level. Then you build all the relationships like And yes, to build it you would have to do some recursive calls possible.. but at least only once for each save (not for every view) in your example (f - b,k b - c,d,ek - l,m) FIX!!: I meant to say (f - b b- c,d,e, K k- l,m) relid stroyid1 storyid2 separation_level 1 fb 0 2 bc 0 3 bd 0 4 be 0 5 fc 1 6 fd 1 7 fe 1 8 kl 0 9 km 0 10 fk 0 11 fl 2 12 fm 2 to get direct relations to f Select storyid2 from storyRelationship where storyid1 = f and separation_level = 0 or remove the separateion_level = 0 clause.. and it will return all related stories reguardless of how. I would use the separation_level to at least Rank how important a story is related to f.. just order by seperation_level so direct relations are first.. The only thing you loose by having all the compinations in the table.. is HOW the relation was made... no way to tell that the reason f relates to c,d,e was because b related to them... If you use the code heirarchy parser, you could maintain that kind of info.. but you probably done need it i think (depends on what your business needs are) Brian so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would
Re: Whats the best way to manage 'generic two-way relationships'?
Hi, - Original Message - From: Hoffman, Geoffrey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 17, 2003 7:01 PM Subject: Whats the best way to manage 'generic two-way relationships'? I couldn't think of a better term for what I want to do... I am building a CMS for news stories. Often news stories are related to each other, or need to be grouped by content. I need a simple way to manage storyId relationships - in both directions. Thinking about the business logic to implement this is a bit confusing, because if you say story f is related to story b, and b is already related to c,d,e, then do I need aditional records for f-c, f-d, f-e? It gets worse if story f is related to g,h,i, and k is related to l,m,n and then you relate f to k... then you'd have to insert additional 9 (or would it be 12) records for the cross-product of all combinations of all the relationships. Or would you? And I can't think of how you'd handle this at the DB level (I'd have to loop in PHP and have 9 inserts?) My first thought was: CREATE TABLE storyRelationship( relIdINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, storyId1 INT UNSIGNED NOT NULL INDEX, storyId2 INT UNSIGNED NOT NULL INDEX In my opinion, is not too well(i don't wish to said it's a big mistake).In this way you will not be able to have data flexibility.The cardinality of your related stories will be 2 for ever. I think you can solve your situation with 5 tables. 1 table for data attribute(category,etc).CATEGORY 2 table for stories - i think in this way because you have 2 entities of relations : TO_RELATION and FROM_RELATION 2 index tables : one table for every relation table which can contain interchangeable ID's of stories:IDX_TO_RELATION which that contain ID's from FROM_RELATION and IDX_FROM_RELATION which that contain ID's from TO_RELATION A little schema (please maximize): FROM_RELATION(FIELDS: IDFROM,IDCAT..) IDX_FROM_RELATION(FIELDS: IDFROM,IDRELATED) CATEGORY(FIELDS: IDCAT...) TO_RELATION(FIELDS: IDTO,IDCAT,.)--IDX_TO_RELATION(FIELDS: IDTO,IDRELATED) relation schema for to_relation can be : CATEGORY.IDCAT-TO_RELATION.IDCAT TO_RELATION-IDX_TO_RELATION.IDTO IDX_TO_RELATION.IDRELATED-FROM_RELATIONS.IDFROM ..and for from_relation can be: CATEGORY.IDCAT-FROM_RELATION.IDCAT FROM_RELATION.IDFROM-IDX_FROM_RELATION.IDFROM IDX_FROM_RELATION.IDRELATED-TO_RELATION.IDTO ...it's very untested what i drawing here but i think it's a good start. I hope it help Best regards, Gelu ); so, for a given target story ID I would need a query that has something like... ... WHERE storyId1 = targetId OR storyId2 = targetId Another way I thought of has its own problems - CREATE TABLE storyRelationship ( relId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, storyIdList TEXT }; of which a record would look something like: relId 5 storyIdList '46,59,89,94,213' but then I would need to do a lot of string parsing to get related stories, and I have to believe that a query with a 'LIKE %id%' clause is going to be a lot slower than one with an OR looking on two indexed rows. Any suggestions? - 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
Where should I start looking? Relationships? Joins? Temp Tables?
Hello, I am trying to create a shopping cart for my company and have hit a snag. I have created a database in MySQL that has several tables. How would I go about displaying the fields from one table based on the fields in the other? Let me explain. I have a table called forder_details which contains the following fields. It has the ordernumber, the productid and the quantity. When a customer puts an item in the cart, it inserts this information into the forder_details database. EXAMPLE: Orderno productidqty 325505 27 3 Everything works fine except when it comes time to display the items in the cart. How do I need to go about telling the cart display page that productid 27 is really an apple and the cost is $1.00 and so on. I will need to do the select on the cart based on a session that contains the order number. I will also need to loop through all of these items as well. Thanks in advance. Chris - 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
Another Defining Relationships in MySQL Question
In MS Access you define relationships between Key ID fields by dragging a line on the screen from the parent table to the child table. So, if you are enforcing referential integrity you can not delete data that is referred to by another table or mess up the basic db structure... (think I have this about right :) When you first create a db in MySQL... how do you tell MySQL which Key field in table A is related to a field in table B? Thanks for any help - tmb __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 - 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: Another Defining Relationships in MySQL Question
tmb wrote: In MS Access you define relationships between Key ID fields by dragging a line on the screen from the parent table to the child table. Read up on referential integrity and InnoDB tables in the online docs at http://www.mysql.com/doc/en -- Michael T. Babcock C.T.O., FibreSpeed Ltd. 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
MySQL Relationships
Hi, I am new to MySQL and was needing to know about MySQL and relationship between tables. Can it be done and if so how? Any docs or show me how would be greatly appreciated. Thanks in advance! - 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: MySQL Relationships
Hi, For estabilish the relationship you have to type the same same sql command what you fire from other RDBMS like oracle.Only the command prompt is mysql in plance of sql. For more detail go to www.mysql.com and read documentations. Thanks, Anjani -Original Message- From: Otoniel Cantu` [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 11:06 AM To: [EMAIL PROTECTED] Subject: MySQL Relationships Hi, I am new to MySQL and was needing to know about MySQL and relationship between tables. Can it be done and if so how? Any docs or show me how would be greatly appreciated. Thanks in advance! - 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
inserting values w/ relationships
Hey gang, I have two tables: user ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | user_id| int(10) unsigned | | PRI | NULL| auto_increment| | first_name | varchar(10) | | | | | | last_name | varchar(20) | | | | | | email | varchar(100) | | | | | | username | varchar(16) | | | | | | password | varchar(32) | | | | | ++--+--+-+-+---+ and quiz +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | quiz_id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | int(10) unsigned | | | 0 || | title | varchar(255) | | | || +-+--+--+-+-++ now user_id and is a relationship between user and quiz... How do i make sure that when i insert a new row into quiz that user_id gets taken from quiz, without me having to look it up in user and entering it manually? any ideas? Jule -- Jule Slootbeek [EMAIL PROTECTED] http://blindtheory.cjb.net ( sql, query ) - 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: inserting values w/ relationships
In the last episode (Jun 07), Jule Slootbeek said: Hey gang, I have two tables: user ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | user_id| int(10) unsigned | | PRI | NULL| auto_increment| | first_name | varchar(10) | | | | | | last_name | varchar(20) | | | | | | email | varchar(100) | | | | | | username | varchar(16) | | | | | | password | varchar(32) | | | | | ++--+--+-+-+---+ and quiz +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | quiz_id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | int(10) unsigned | | | 0 || | title | varchar(255) | | | || +-+--+--+-+-++ now user_id and is a relationship between user and quiz... How do i make sure that when i insert a new row into quiz that user_id gets taken from quiz, without me having to look it up in user and entering it manually? any ideas? INSERT INTO users VALUES (NULL,'myfname','mylname','myemail','username','pass'); INSERT INTO quiz VALUES (NULL,LAST_INSERT_ID(), 'title goes here'); -- Dan Nelson [EMAIL PROTECTED] $#$@! list filter. sql, query. there. happy? - 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
Building relationships between tables
Hello friends, In Access, we build relationships between tables and keys, How do we do this between keys in mysql? Thanks Denis - 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: Building relationships between tables
Denis, Wednesday, May 01, 2002, 1:09:07 PM, you wrote: DLM In Access, we build relationships between tables and keys, How do we do this DLM between keys in mysql? What about foreign keys? Look at the manual, you can find some info about foreign keys in MySQL: http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html http://www.mysql.com/doc/e/x/example-Foreign_keys.html http://www.mysql.com/doc/S/E/SEC445.html DLM Thanks DLM Denis -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Buliding relationships between tables - how?
Hello friends, Can someone tell me how I can build the relationships between tables in mysql? I have tried books and web manuals. Even a link will do. Thanks Denis - 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: I thought MySQL Supported Relationships
At 06:04 PM 23/04/2002 -0700, you wrote: http://www.google.com/search?hl=enq=Views+Mysql ... MySQL Manual | 1.7.4.6 Views ... It is planned to implement views in MySQL Server around Version 4.1. Views are mostly useful for letting users access a set of relations as one table (in read ... www.mysql.com/doc/A/N/ANSI_diff_Views.html - 17k - Cached - Similar pages MySQL Manual | 1.7.3.7 Views ... One doesn't need views in MySQL to restrict access to columns as MySQL has a very sophisticated privilege system. See section 4.2 General Security Issues and ... www.mysql.com/doc/M/i/Missing_Views.html - 9k - Cached - Similar pages [ More results from www.mysql.com ] Views made it possible to implement one of the most ambitious projects at a company I worked for. The project involved a new methodology that I stole - er - developed. It used a modular data model that let us model the entire corporation in under a year when the original estimate was in excess of 5 years. We were able to bring SECTIONS of the system to production a blinding speed. As an example, we brought one section up and running a full year before we were scheduled to start. It seems that one of the programmers had a free weekend and did it all - instead of taking 3 programmers a year. None of it would have been possible if we had not had views (Oracle on the mainframe and on PCs) to isolate the inner complexity from the application. I'd love to try such a project with MySQL. Here's hoping that 4.1 isn't too far off. -Original Message- From: Arthur Fuller [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 5:57 PM To: Jonathan Hilgeman; [EMAIL PROTECTED] Subject: Re: I thought MySQL Supported Relationships Perhaps I fail to catch the humour in your response, or the flavour of this newsgroup, but I find your answer in no way helpful, and beyond that quite insulting to Access. 1. I have developed applications in Access comprising over 1000 tables, some of which contain millions of rows, with numerous complex relationships and C++ components that interfaced to instruments such as scales, scanners, etc. I am not the only one to push Access that far. 2. To answer Andrew's question, the answer is No. MySQL does not support defined relationships. The InnoDB extension provides this support. 3. Unfortunately, as of this writing neither MySQL nor InnoDB supports defined queries (Oracle and SQL Server call them Views). Allegedly, MySQL 4.1 will support stored procedures (and I for one hope that views make it into the build). Arthur - Original Message - From: Jonathan Hilgeman [EMAIL PROTECTED] To: 'Andrew Kuebler' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 2:43 PM Subject: RE: I thought MySQL Supported Relationships Actually, no. The addiction to MySQL has caused many relationship break-ups. There's probably even a database with that information out there somewhere. Probably a MySQL database, too. Meanwhile Access supports relationships because it blows so hard that people can't wait to get away from it and home to their families. - Jonathan -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: I thought MySQL Supported Relationships Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - 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
I thought MySQL Supported Relationships
Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - 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: I thought MySQL Supported Relationships
Actually, no. The addiction to MySQL has caused many relationship break-ups. There's probably even a database with that information out there somewhere. Probably a MySQL database, too. Meanwhile Access supports relationships because it blows so hard that people can't wait to get away from it and home to their families. - Jonathan -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: I thought MySQL Supported Relationships Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - 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: I thought MySQL Supported Relationships
* Andrew Kuebler I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! It is called 'joins', and it is mainly used within the SELECT statement: URL: http://www.mysql.com/doc/S/E/SELECT.html URL: http://www.mysql.com/doc/J/O/JOIN.html -- Roger - 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: I thought MySQL Supported Relationships
On Tue, 2002-04-23 at 14:35, Andrew Kuebler wrote: Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! check out http://www.innodb.com/ -Jason - 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: I thought MySQL Supported Relationships
Perhaps I fail to catch the humour in your response, or the flavour of this newsgroup, but I find your answer in no way helpful, and beyond that quite insulting to Access. 1. I have developed applications in Access comprising over 1000 tables, some of which contain millions of rows, with numerous complex relationships and C++ components that interfaced to instruments such as scales, scanners, etc. I am not the only one to push Access that far. 2. To answer Andrew's question, the answer is No. MySQL does not support defined relationships. The InnoDB extension provides this support. 3. Unfortunately, as of this writing neither MySQL nor InnoDB supports defined queries (Oracle and SQL Server call them Views). Allegedly, MySQL 4.1 will support stored procedures (and I for one hope that views make it into the build). Arthur - Original Message - From: Jonathan Hilgeman [EMAIL PROTECTED] To: 'Andrew Kuebler' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 2:43 PM Subject: RE: I thought MySQL Supported Relationships Actually, no. The addiction to MySQL has caused many relationship break-ups. There's probably even a database with that information out there somewhere. Probably a MySQL database, too. Meanwhile Access supports relationships because it blows so hard that people can't wait to get away from it and home to their families. - Jonathan -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: I thought MySQL Supported Relationships Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - 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
RE: I thought MySQL Supported Relationships
http://www.google.com/search?hl=enq=Views+Mysql ... MySQL Manual | 1.7.4.6 Views ... It is planned to implement views in MySQL Server around Version 4.1. Views are mostly useful for letting users access a set of relations as one table (in read ... www.mysql.com/doc/A/N/ANSI_diff_Views.html - 17k - Cached - Similar pages MySQL Manual | 1.7.3.7 Views ... One doesn't need views in MySQL to restrict access to columns as MySQL has a very sophisticated privilege system. See section 4.2 General Security Issues and ... www.mysql.com/doc/M/i/Missing_Views.html - 9k - Cached - Similar pages [ More results from www.mysql.com ] ... C:~ -Original Message- From: Arthur Fuller [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 5:57 PM To: Jonathan Hilgeman; [EMAIL PROTECTED] Subject: Re: I thought MySQL Supported Relationships Perhaps I fail to catch the humour in your response, or the flavour of this newsgroup, but I find your answer in no way helpful, and beyond that quite insulting to Access. 1. I have developed applications in Access comprising over 1000 tables, some of which contain millions of rows, with numerous complex relationships and C++ components that interfaced to instruments such as scales, scanners, etc. I am not the only one to push Access that far. 2. To answer Andrew's question, the answer is No. MySQL does not support defined relationships. The InnoDB extension provides this support. 3. Unfortunately, as of this writing neither MySQL nor InnoDB supports defined queries (Oracle and SQL Server call them Views). Allegedly, MySQL 4.1 will support stored procedures (and I for one hope that views make it into the build). Arthur - Original Message - From: Jonathan Hilgeman [EMAIL PROTECTED] To: 'Andrew Kuebler' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 2:43 PM Subject: RE: I thought MySQL Supported Relationships Actually, no. The addiction to MySQL has caused many relationship break-ups. There's probably even a database with that information out there somewhere. Probably a MySQL database, too. Meanwhile Access supports relationships because it blows so hard that people can't wait to get away from it and home to their families. - Jonathan -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: I thought MySQL Supported Relationships Hello Everyone. I'm still fairly a newbie to MySQL, but I could have sworn I read somewhere that MySQL supported relationships just like Access. I've searched the entire manual, and cannot find anything on the subject. Does MySQL support relationships, and if so does anyone know where I can find any info on the topic? Thanks! Andrew - 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 - 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
relationships between databases
This is my first post to the list What is the best way of making a relationship between tables in Mysql. Do you set it up when you are creating the tables or is it done with the insert statement I am a newbie to Mysql, BTW Guy - 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: relationships between databases
You need to have some sort of unique identifier, like a primary key. And all the tables should agree upon that Defryn, Guy wrote: DG: Date: Tue, 18 Dec 2001 10:37:41 +1300 DG: From: Defryn, Guy [EMAIL PROTECTED] DG: To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] DG: Subject: relationships between databases DG: DG: DG: DG: This is my first post to the list DG: DG: What is the best way of making a relationship between tables in Mysql. DG: Do you set it up when you are creating the tables or is it done with the DG: insert statement DG: DG: I am a newbie to Mysql, BTW DG: DG: Guy DG: DG: - DG: Before posting, please check: DG:http://www.mysql.com/manual.php (the manual) DG:http://lists.mysql.com/ (the list archive) DG: DG: To request this thread, e-mail [EMAIL PROTECTED] DG: To unsubscribe, e-mail [EMAIL PROTECTED] DG: Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php DG: -- Sherzod Ruzmetov [EMAIL PROTECTED] http://www.UltraCgis.com, Consultant 989.774.6265 010010100101010101001100 ++ | There is nothing wrong with your tools.| | But we can make a better one. | ++ - 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
Tables relationships
Hi folks, Can someone please point me in the right direction to let me know how to define a relationship between tables in MySQL eg one-to-many. I haven't come across it in the manual yet, but am sure it must be there somewhere. Many thanks in advance, Chris Malloch
Re: Tables relationships
Dear Chris, look here: http://www.mysql.com/doc/J/O/JOIN.html It's one of the JOINs (INNER JOIN or LEFT JOIN) you are looking for. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Chris Malloch [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 07, 2001 10:54 AM Subject: Tables relationships Hi folks, Can someone please point me in the right direction to let me know how to define a relationship between tables in MySQL eg one-to-many. I haven't come across it in the manual yet, but am sure it must be there somewhere. Many thanks in advance, Chris Malloch - 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: Relationships???
Referential integrity should be maintained by the program (programmer) using the file handler/database. We design all of our schemas with a Parent/Child hierarchy in mind. We always have a unique key and there is always a column or key in common where any relationship exists or could exist between tables. Consider an invoice with a 1-many relationship. Header information regarding the sold to, ship to, etc.. and the Detail containing the items, quantity, etc... . These two tables would have an invoice number field/column in common. The program used to Add,Edit,Delete would enforce the relationship, thereby, maintaining referential integrity. The concept of a DBA (read 'not a programmer') attempting to enforce referential integrity by pure schema design without consideration of the applications designed to use the data or the unlimited creativity of the end-user at the keyboard over simplifies the process and itself imposes data integrity questions. Pat... . - Original Message - From: Rolf Hopkins [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 14, 2001 9:28 PM Subject: Re: Relationships??? If you are really after referential integrity then perhaps you should consider another database or you could always modify the code. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 15, 2001 8:32 Subject: Relationships??? Hello all, Without explicitly creating foreign keys, what is the most common method to create a truly relational database that enforces referential integrity? Any responses to this question are greatly appreciated! Sincerely, N. Silverwise - 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
