Re: Tables and foreign keys

============
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

> 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
>
>