Jump to content

Zero or One Relationship


SaranacLake
 Share

Recommended Posts

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
Link to comment
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?

Link to comment
Share on other sites

@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
Link to comment
Share on other sites

This thread is more than a year old.

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.

 Share

×
×
  • 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.