SaranacLake Posted August 9, 2020 Share Posted August 9, 2020 (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 August 9, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/311303-zero-or-one-relationship/ Share on other sites More sharing options...
requinix Posted August 10, 2020 Share Posted August 10, 2020 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? Quote Link to comment https://forums.phpfreaks.com/topic/311303-zero-or-one-relationship/#findComment-1580502 Share on other sites More sharing options...
SaranacLake Posted August 10, 2020 Author Share Posted August 10, 2020 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? Quote Link to comment https://forums.phpfreaks.com/topic/311303-zero-or-one-relationship/#findComment-1580504 Share on other sites More sharing options...
SaranacLake Posted August 10, 2020 Author Share Posted August 10, 2020 (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 August 10, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/311303-zero-or-one-relationship/#findComment-1580505 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.