Jump to content

Zero or One Relationship


Recommended Posts

I have the following logical ERD design...



And here are the (abridged) tables...

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


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


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


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



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

    1                    33              5027
    2                    33              9402
    3                    33              3371
    4    SESS_111                        8269
    5    SESS_111                        9077







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.

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.