Re: Tables and foreign keys

Thank you!

On Dec 26, 2011, at 4:12 AM, Amit Tandon wrote:

> Dear Karl
>
> Refer Wiki entry. The last diagram shows one to many relationship in  
> many ways. That may help you in clearing your doubts about the comma  
> which is a way of representing the relationship
>
> Quoted from MS Access example
>
> One-To-Many Relationships
>
> A one-to-many relationship is the most common type of relationship.  
> In this type of relationship, a row in table A can have many  
> matching rows in table B, but a row in table B can have only one  
> matching row in table A. For example, the Publishers and Titles  
> tables have a one-to-many relationship: each publisher produces many  
> titles, but each title comes from only one publisher.
>
> A one-to-many relationship is created if only one of the related  
> columns is a primary key or has a unique constraint.
>
> Many-To-Many Relationships
>
> In a many-to-many relationship, a row in table A can have many  
> matching rows in table B, and vice versa. You create such a  
> relationship by defining a third table, called a junction table,  
> whose primary key consists of the foreign keys from both table A and  
> table B. For example, the Authors table and the Titles table have a  
> many-to-many relationship that is defined by a one-to-many  
> relationship from each of these tables to the TitleAuthors table.  
> The primary key of the TitleAuthors table is the combination of the  
> au_id column (the authors table’s primary key) and the title_id  
> column (the Titles table’s primary key).
> One-To-One Relationships
>
> In a one-to-one relationship, a row in table A can have no more than  
> one matching row in table B, and vice versa. A one-to-one  
> relationship is created if both of the related columns are primary  
> keys or have unique constraints.
> ============
> regds
> amit
>
> "The difference between fiction and reality? Fiction has to make  
> sense."
>
>
> On Mon, Dec 26, 2011 at 2:43 PM, Karl DeSaulniers <[email protected] 
> > wrote:
>
> On Dec 26, 2011, at 3:05 AM, Amit Tandon wrote:
>
> ============
> regds
> amit
>
> "The difference between fiction and reality? Fiction has to make  
> sense."
>
>
> On Mon, Dec 26, 2011 at 1:55 PM, Karl DeSaulniers <[email protected] 
> >wrote:
>
> On Dec 26, 2011, at 1:57 AM, Amit Tandon wrote:
>
> Dear Karl
>
> Foreign key is a good option (provided you use InnoDB database) to
> automate
> the deletion/updation of cart details when you remove cart.
>
> Besides 1:1 cardinality is good or bad can only be decided on the
> semantics
> of your tables.
>
> As a an aside, if it is 1:1 cardinality, then you can probably merge  
> the
> two tables
> ============
> regds
> amit
>
> "The difference between fiction and reality? Fiction has to make  
> sense."
>
>
> On Mon, Dec 26, 2011 at 12:38 PM, Karl DeSaulniers <[email protected]
> wrote:
>
> Hello all,
> I have two database tables. Cart and a cartdetails.
> I want the information for the cartdetails to be based on/autofilled  
> info
> from cart.
> I want the ability to delete from cart and cartdetails to  
> automatically
> clear as well.
> Would a foreign key be best suited for this? What kind of cardnaility
> would I use 1:1 ?
> Hope I make sense.
>
> Hope everyone had a great holidays!
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
>
> Thanks Amit for the reply.
> Ok, so if you can not merge the two tables, then it is not a 1:1
> relationship?
>
> EG:
>
> (table)CART:
>      cartID - PRIMARY
>      username
>      firstname
>      lastname
>      product
>      long description
>      quantity
>      options
>      price
>
> (table)CARTDETAILS:
>      cartdetailsID
>      cartID - Foreign Key (Unique)
>      product
>      short description (taken from long des. in CART)
>      quantity
>      options
>      price
>
> Is this a 1:1 if the key is between the two cartID's?
>
> No! it is 1:N becuase of Multiple rows in Cartid = 1 row in Cart
>
>
> What is actually happening when I make a foreign key?
>
> You link the two tables and try to find matching rows of cart in  
> cart id
>
> What does the foreign key cover? Just the row its linked to or the  
> whole
> table?
>
> The foreing key helps you uniquely identift set of rows. Foerign key
> actully help you identify the row(s). And then column of thos rows  
> give you
> your value
>
> For this structure I would like, in order for their to be cartdetails,
> there should be a cart.
> If there is a cart then a cartdetails needs to automatically/ 
> progmatically
> generate.
> (I think I read somewhere that MySQL is capable of doing this before
> sending back responses. Please correct me if I am wrong or if its
> unrelated.)
>
> What kind of relation is that?
> Also, if you or someone could explain what these cardinality rules  
> mean.
> What..
>
> 0 or 1  does?
>
> 1:1
>
> 1+       does?
>
> 1:M
>
> 0,1+    does?
>
> M:N - you figure correctly. But this is for both the tables i.e 1+
> psosibility in both the tables. And this is generally table which is  
> linked
> to two tables
>
> So the 0=first table and ,1+ = subsezuent tables?
> Is there such thing as 1+,0? Or is that what 1+ is?
> Sry, the coma throws me off a bit.
>
>
>
> Many (kind-of figured this one out, perhaps not.)
> etc..
>
> I think that would help me greatly. I hope I am not sounding like a  
> help
> vamp.
> I only ask because my google foo has failed me on where to search for
> these specific answers.
> Links are appreciated. I like to do stuff on my own but sometimes the
> brain cramps. :)
> TIA
>
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
>
> Thanks..
> I think that got the knot out a little.. :)
>
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com