Re: Tables and foreign keys

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 <karl@designdrumm.comwrote:

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 <karl@designdrumm.com

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