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]
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]
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 - 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: 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]
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]>TEL 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: 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
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
Re: Relationships???
[EMAIL PROTECTED] wrote: > 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 > Referential Integrity is a two-edged sword. If you aren't careful you can rip the guts out of your db with interlaced cascading deletes. You generally need pin-point control in a complex relation. I prefer the finer control I have doing it in code. If you are looking for a completely compliant RDBMS then you shall be looking for some years yet, as ALL of the offerings out there depart to some degree or another based on the design decisions of the developers. danh - 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???
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
