Jump to content
SaranacLake

Zero or One Relationship

Recommended Posts

Posted (edited)

I have the following logical ERD design...

	MEMBER -|0------0|- SHOPPING_CART
	

 

And here are the (abridged) tables...

	MEMBER
	- id (PK)
	- username (UK)
	- email (UK)
	

 

	SHOPPING_CART
	- id (pk)
	- session_id (UK)
	- member_id (FK1)(UK)
	- product_id (FK2)(UK)
	- cart_price
	- quantity
	

 

The logic is this...

1.) A Member can have zero or one Shopping_Carts.  (As a Member, maybe you're shopping, and maybe you aren't.)

2.) A Shopping_Cart can have zero or one Members.  (If a Member is shopping, then they would have a Shopping_Cart tied to their "id".  But if you are a new shopper, and not yet a Member, then the Member record doesn't exist, so there is no linkage to the Member table.  However, I am storing the SessionID in the Shopping_Cart so I can still link it to you.)

 

Modeling this logically is easy enough, but I'm not sure of the physical implementation.

 

I created a FK Constraint last night on on SHOPPING_CART.member_id as shown in (FK1) above, but won't that prevent me from placing an item into the shopping cart if there is not a corresponding Member??

 

Edited by SaranacLake

Share this post


Link to post
Share on other sites

Yup.

If you go to a grocery store to pick up milk and eggs, do you get one shopping cart for the milk and another shopping cart for the eggs?

Share this post


Link to post
Share on other sites
1 hour ago, requinix said:

Yup.

If you go to a grocery store to pick up milk and eggs, do you get one shopping cart for the milk and another shopping cart for the eggs?

I don't think you understood my question above.

Regardless of my question, the design I described above could have one or many records, but they would all be tied to either the same "member_id" OR to the same "session_id", thus constituting ONE shopping_cart per person.

 

Just did a test, and since SHOPPING_CART.member_id is set to allow Nulls, I was able to insert a shopping_cart item/record WITHOUT needing to have a Member.

So I think that allowing Nulls on member_id is what gives me the "zero" in "A Shopping_Cart can have zero to one Members..."

 

Does that seem like an okay design?

Share this post


Link to post
Share on other sites
Posted (edited)

@requinix

Oops!  Looks I misspoke in my last post.

I was correct that allow a Null member_id addressed what this thread was about.  But I now see what you were saying...

I guess my ERD should look like this...

MEMBER -|0------0<- SHOPPING_CART ->0------||- PRODUCT


 

xxx

    That is because my Shopping_Cart would look like this...
    
   

    ID    SESSION_ID    MEMBER_ID    PRODUCT_ID
    1                    33              5027
    2                    33              9402
    3                    33              3371
    4    SESS_111                        8269
    5    SESS_111                        9077
    

 

 

xxx

 

 

 

Edited by SaranacLake

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.