Re: Tables and foreign keys

Dear KarlRefer Wiki
entry<http://en.wikipedia.org/wiki/Entity-relationship_model>.
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
>
>