SaranacLake Posted May 10, 2020 Share Posted May 10, 2020 I am working on the e-commerce portion of my website that sells online subscriptions, books, gear, etc. My desk is covered with ERD's right nw, and I thought I had things pretty much figured out, but discovered a curve ball... Originally for subscriptions, I did this... SUBSCRIPTION_DETAILS - id - member_id - plan_id - purchased_on - subscription_start_date - subscription_end_date - purchase_price and so on... But after I mapped out the tables related to ordering (i.e. MEMBER, ORDER, ORDER_DETAILS, PRODUCT) it occurred to me that I have two annoying issues... Issue #1: Some of the fields in SUBSCRIPTION_DETAILS really could be moved, for example... subscription_details.purchased_on ===> order.ordered_on subscription_details.purchase_price ===> order_details.purchase_price Issue #2: Currently I have no linkage between my ORDER and SUBSCRIPTION_DETAILS. Questions: a.) Would it be the end of the world to have duplicate fields and when a person subscribes, just write the fields in Issue #1 to two places? (The ORDER / ORDER_DETAILS tables help keep track of a purchase. And the SUBSCRIPTION_DETAILS table help s keep track of members' subscriptions. They are related in some ways, yet moreso separate.) I could move those fields to one place as mentioned above, but then that makes querying things to calculate things like renewal costs and stuff a real PITA. I could also put an "Order_No" in the SUBSCRIPTION_DETAILS table as a cross-reference, but again, that makes querying things a lot more work. (I have a fairly elaborate way of calculating subscription and renewal pricing, which is why I subconsciously put all of those fields originally in my SUSBCRIPTION_DETILS table! Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/ Share on other sites More sharing options...
Barand Posted May 10, 2020 Share Posted May 10, 2020 Here we go again.. We tell you how it should be done then get involved in several pages of posts by you telling us you don't want to do it that way. I'll pass. JFDI. (BTW, what did you think of MySQL Workbench - or is your motto "Procrastinate now!") Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577821 Share on other sites More sharing options...
SaranacLake Posted May 10, 2020 Author Share Posted May 10, 2020 2 minutes ago, Barand said: Here we go again.. We tell you how it should be done then get involved in several pages of posts by you telling us you don't want to do it that way. I have *never* posted anything about the above question until now! So what are you talking about? 2 minutes ago, Barand said: I'll pass. JFDI. (BTW, what did you think of MySQL Workbench - or is your motto "Procrastinate now!") Clearly you've developed some deep-seated anger towards me, which is a surprise, because you've been very helpful in the past. If I upset you so much, then best to skip my threads moving forward... Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577822 Share on other sites More sharing options...
Barand Posted May 10, 2020 Share Posted May 10, 2020 54 minutes ago, SaranacLake said: I have *never* posted anything about the above question until now! I didn't say you had. It was a comment on the general pattern with your posts. And I'm not angry at all. It's just exasperation with the futility of trying to help. 52 minutes ago, SaranacLake said: If I upset you so much, then best to skip my threads moving forward Can do. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577825 Share on other sites More sharing options...
SaranacLake Posted May 11, 2020 Author Share Posted May 11, 2020 (edited) Do I necessarily have redundant data if I write the same date to two separate fields in separate tables? For instance, if a person buys a subscription today on May 10, am I breaking normalization if I have: Order.ordered_on = 2020-05-10 Subscription_Details.purchased_on = 2020-05-10 One date focuses on the ORDER which is needed for a record of the purchase, and the other date focuses on the SUBSCRIPTION and impacts renewal pricing. Thoughts? Edited May 11, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577833 Share on other sites More sharing options...
gizmola Posted May 11, 2020 Share Posted May 11, 2020 I would want to see an ERD of the tables involved in this question. If order and subscription_details have no relationship to each other, then why do you have them both? A typical commerce system would at minimum have: Order --< lineitem This allows people to purchase multiple things in one order. The usual way this might be handled would be to have an item that describes the thing being purchased. So the extended model including lineitem: Order --< lineitem >-- Item Items I would expect: 1 Month Subscription 3 Month Subscription 6 Month Subscription 12 Month Subscription 2 Year Subscription Each of these will have a price, although the price usually would be in a seperate table related to item, broken out with an effective from-to date. So in conclusion, it looks like your subscription_plan = lineitem (although not generically capable of supporting sale of things other than subscriptions) and plan = item. I'm not sure why you are making this so specific to only subscriptions, nor am I clear how order fits into this. Having a lineitem date would be unusual in a system like this. If I order a stick of gum and a magazine on tuesday, I don't need a separate stick of gum date & magazine date, which is going to be the same as the order date. Renewals is an aging process in subscription systems. You run batch processes that do things like: -send out 1 month reminder -send out 15 days reminder -send out discount offers send out expiration notice -send out churn offers (come back and I'll offer you 10% off) etc. These operations don't require an end date. What they do require is a start date, and the subscription status and duration. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577875 Share on other sites More sharing options...
SaranacLake Posted May 12, 2020 Author Share Posted May 12, 2020 @gizmola, Since this thread got sidetracked as discussed, precious time was lost, and i believe I figured things out about 3:00am this morning. Let me try and reply to your questions, understanding that I am leery in this case to post a large some of my data model. I always enjoy philosophizing, so will respond with what I can. 2 hours ago, gizmola said: I would want to see an ERD of the tables involved in this question. If order and subscription_details have no relationship to each other, then why do you have them both? You and someone in Beijing are related if you go back to Adam and Eve, but there is no immediate connection, right? 😃 2 hours ago, gizmola said: A typical commerce system would at minimum have: Order --< lineitem This allows people to purchase multiple things in one order. The usual way this might be handled would be to have an item that describes the thing being purchased. So the extended model including lineitem: Order --< lineitem >-- Item Yes, I have the textbook... MEMBER -||-----0<- ORDER -||------|<- ORDER_DETAILS ->0-----||- PRODUCT But I also have... PRODUCT (supertype) --||-----0|- MEMBER_PLAN (subtype) MEMBER -||-----|<- SUBSCRIPTON_DETAILS ->0-----||- MEMBER_PLAN 2 hours ago, gizmola said: Items I would expect: 1 Month Subscription 3 Month Subscription 6 Month Subscription 12 Month Subscription 2 Year Subscription Each of these will have a price, although the price usually would be in a seperate table related to item, broken out with an effective from-to date. So in conclusion, it looks like your subscription_plan = lineitem (although not generically capable of supporting sale of things other than subscriptions) and plan = item. The simple answer is that a MEMBER_PLAN has multiple MEMBER_PLAN_VERSION which includes pricing logic/details. And what is in the MEMBER_PLAN_VERSION table drives what gets stored in SUBSCRIPTION_DETAILS and it would not be appropriate to store that in ORDER_DETAILS. I *think* can strip out things like "purchased_on" and "purchase_price" from SUBSCRIPTION_DETAILS, it just creates a lot more work (i.e. complex functions and queries) to get that info so I can calculate the pricing. 2 hours ago, gizmola said: I'm not sure why you are making this so specific to only subscriptions, nor am I clear how order fits into this. Simple answer is because the way I set the subscription price and renewal price depends on WHAT you did and WHEN you did it. 2 hours ago, gizmola said: Having a lineitem date would be unusual in a system like this. If I order a stick of gum and a magazine on tuesday, I don't need a separate stick of gum date & magazine date, which is going to be the same as the order date. If I follow what you are saying, then I agree, but originally I put the SUBSCRIPTION_DATE.purchase_date in so I had all of the data I needed in one place to calculate subscription pricing. Yes, that is denormalization, but in the real world it is not unheard of. And since a "purchased_on" value should never be changed, I wasn't too worried about getting bitten. But I think it isn't so bad if I use ORDER.ordered_on. Since this is on paper, I think my ERD is evolved enough that this is one time where it is better to just start coding, and I think the answer will reveal itself. 2 hours ago, gizmola said: Renewals is an aging process in subscription systems. You run batch processes that do things like: -send out 1 month reminder -send out 15 days reminder -send out discount offers send out expiration notice -send out churn offers (come back and I'll offer you 10% off) etc. These operations don't require an end date. What they do require is a start date, and the subscription status and duration. Short answer... MEMBER_PLANS have VERSIONS, and VERSIONS have price-points. And when you subscribe and when you renew is based on the VERSION snapshot in time, so I do need a start and end date. You subscribed on May 10, 2020 for $50 and it runs through May 10, 2021? Well, if you renew before May 10, 2021 then i grandfather your subscription, but afterwards it is whatever VERSION applies to that renewal date. And I have this fancy logic - which I am barely touching on - because if you keep renewing, you could be paying $50/year when in 2025 the subscription price is $150/year! Even if you could see everything, I think you'd tell me NOT to have redundant data. And that is the gameplan. If it turns out not too difficult to do, I'll do it the pure way. if not, then I think it's okay to break normalization this one time. Oh, as I recall, I think I also need to add the "Order_No" to my SUBSCRIPTION_DETAILS table so I can link things back to the ORDER, because as it stands, there is no easy way to link things, which is another reason for my original design. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577881 Share on other sites More sharing options...
SaranacLake Posted May 12, 2020 Author Share Posted May 12, 2020 Okay, I spent the last hour or so looking at my data model again. Like most things, there is no one perfect solution. But being a purist, I have decided to do this... Remove the redundant "purchased_on" and "purchase_price", leave then on ORDER and ORDER_DETAILS, and then add "order_id" to my SUBSCRIPTION_DETAILS table, so I have a way to link a particular purchase to a particular subscription - which is NOT the same thing as a record in ORDER_DETAILS. I guess that would be creating a loose one-to-one link between my ORDER and SUBSCRIPTION_DETAILS tables? Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577884 Share on other sites More sharing options...
gizmola Posted May 12, 2020 Share Posted May 12, 2020 5 hours ago, SaranacLake said: You and someone in Beijing are related if you go back to Adam and Eve, but there is no immediate connection, right? 😃 This is database design terminology. A relation is the connection between 2 tables. 5 hours ago, SaranacLake said: PRODUCT (supertype) --||-----0|- MEMBER_PLAN (subtype) In a relational database there is no such thing as a subtype. You can support the idea by having something like product_type, where the various subtypes you want to support are enumerated. Then for every subtype, you need a table. So in your case, the one we know about is "membership". Let's say that Product.type can have these values: Article Membership Clothing Then you would expect to have these tables: Product_article Product_membership Product_clothing You make a dependent relationship between Product and the subtype table, so that it's primary key is the same as the Product primary key. The "type" column in Product indicates which subtype table you would expect needed to be joined to. ORM's like Doctrine2 and Laravel Eloquent have a degree of support for this. Pricing should not be related to the subtype table, but instead be related to the Parent product table. When a membership is ordered, you would have the Product linked to the order, not the subtype table. There are practical reasons for designing things this way. I can't write a full relational database design treatise on these design patterns or why they work and are maintainable, database driven and easy to code for. 5 hours ago, SaranacLake said: And I have this fancy logic - which I am barely touching on - because if you keep renewing, you could be paying $50/year when in 2025 the subscription price is $150/year! I don't think that's fancy. I'd probably have an attribute in the Product_membership subtype table that could be as simple as a flag that indicates "plan_can_be_renewed" or something like that. Again the aging logic of plan renewals would take this into account when the logic runs to offer the member a renewal. This would really be the reward for the pain of engineering around a subtype table schema which defies standard sql joining of tables. Overall, it seems that most of your tables are just named in a way that is specific to your ideas about how you plan to implement memberships. Hopefully I've headed you off a mistake in regards to Product and Member_plan. The way to think of member_plan is that it's a home for those things that are specific to a membership. As I mentioned I wouldn't name it the way you are planning to name it. It should be named conventionally to indicate the product type as I explained above. 5 hours ago, SaranacLake said: Even if you could see everything, I think you'd tell me NOT to have redundant data. And that is the gameplan. I would probably tell you to use common relational design patterns. I'd also point out places where you are not normalizing. I've done much more database design work as a system architect than most people, so I have a lot of experience with relational design patterns and understand the limitations and implications of a series of tables. Your tables (albeit with some different naming) seem to be close to what I'd assume you would need for a system like this, with the main issue being what appears to me to be a misunderstanding of relational subtyping. Even with that, you absolutely would expect to have an order for a membership with a lineitem for that membership with the foreign key to the product, and the price paid in the lineitem + tax if applicable. Nothing should go into Product_membership that isn't entirely specific to memberships. Things that should be in Product and not in membership would include status (active, inactive, deleted), category_id, name, description, manufacturer id, taxability, etc. Also as I mentioned previously, I'd expect that there would be a price table related to Product, with the price and from_to dates. In this case I would severely question the need for a subtype table extension, if there is only an attribute or 2 specific to membership. If it's only going to be "duration" and "can_be_renewed" flag, I don't think I'd bother. I'd just stick a couple of extra columns in Product. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577885 Share on other sites More sharing options...
SaranacLake Posted May 13, 2020 Author Share Posted May 13, 2020 @gizmola, 17 hours ago, gizmola said: In a relational database there is no such thing as a subtype. Oh? For as long as I have been working with databases, that concept has existed. Are you saying it not formal E.F. Codd stuff, or what? 17 hours ago, gizmola said: You can support the idea by having something like product_type, where the various subtypes you want to support are enumerated. Then for every subtype, you need a table. So in your case, the one we know about is "membership". Let's say that Product.type can have these values: Article Membership Clothing Then you would expect to have these tables: Product_article Product_membership Product_clothing You make a dependent relationship between Product and the subtype table, so that it's primary key is the same as the Product primary key. Yes, I found a good example online of how to implement it in MySQL since there are no check constraints - at least in my version of MySQL. 17 hours ago, gizmola said: Pricing should not be related to the subtype table, but instead be related to the Parent product table. Why? The pricing for each subtype is unique. And the reason I introduced subtypes is because Products vary enough that it is dumb to have a "one size fits all" design. 17 hours ago, gizmola said: When a membership is ordered, you would have the Product linked to the order, not the subtype table. For the Order, yes. For the Subscription, no. The Order "stream" links a MEMBER to an ORDER and ultimately a PRODUCT. The Order "stream" does not specify which PRODUCT_VERSION the MEMBER subscribed to, nor anything about the SUBSCRIPTION. That is the catch. Fwiw, i stayed up until 4am this morning, and I got it all figured out and I stand behind my design, which should be totally 3NF. We'll see how things go when I start to code it this weekend?! 17 hours ago, gizmola said: There are practical reasons for designing things this way. I can't write a full relational database design treatise on these design patterns or why they work and are maintainable, database driven and easy to code for. I don't think that's fancy. I'd probably have an attribute in the Product_membership subtype table that could be as simple as a flag that indicates "plan_can_be_renewed" or something like that. Again the aging logic of plan renewals would take this into account when the logic runs to offer the member a renewal. This would really be the reward for the pain of engineering around a subtype table schema which defies standard sql joining of tables. I have that in my design plus a lot more to help "incentivize" renewals. 17 hours ago, gizmola said: Overall, it seems that most of your tables are just named in a way that is specific to your ideas about how you plan to implement memberships. Hopefully I've headed you off a mistake in regards to Product and Member_plan. The way to think of member_plan is that it's a home for those things that are specific to a membership. Which includes pricing. 😉 17 hours ago, gizmola said: As I mentioned I wouldn't name it the way you are planning to name it. It should be named conventionally to indicate the product type as I explained above. What part don't you like as far as naming goes? 17 hours ago, gizmola said: I would probably tell you to use common relational design patterns. I'd also point out places where you are not normalizing. I've done much more database design work as a system architect than most people, so I have a lot of experience with relational design patterns and understand the limitations and implications of a series of tables. Was there a place I was not normalizing based on the little I told you? 17 hours ago, gizmola said: Your tables (albeit with some different naming) seem to be close to what I'd assume you would need for a system like this, with the main issue being what appears to me to be a misunderstanding of relational subtyping. Again, I know I didn't give you much, but what do you feel I got wrong? 17 hours ago, gizmola said: Even with that, you absolutely would expect to have an order for a membership with a lineitem for that membership with the foreign key to the product, and the price paid in the lineitem + tax if applicable. I have that. 17 hours ago, gizmola said: Nothing should go into Product_membership that isn't entirely specific to memberships. I removed the duplicate "purchaed_on" and "purchase_price", so that solves my initial concern in this thread. (Turns out I don't need lots of fancy queries, and that I can take care of a lot of the decisioning before things hit my tables.) 17 hours ago, gizmola said: Things that should be in Product and not in membership would include status (active, inactive, deleted), category_id, name, description, manufacturer id, taxability, etc. Columns that apply to *all* subtypes would go into the supertype, products. Agree? 17 hours ago, gizmola said: Also as I mentioned previously, I'd expect that there would be a price table related to Product, with the price and from_to dates. I put that in my subtypes, because the way I price a service like subscriptions is radically different that T-shirts. If an attribute applies to all subtypes then it makes sense to go in the supertype, otherwise it goes into a specific subtype which is the whole purpose of subtypes. 17 hours ago, gizmola said: In this case I would severely question the need for a subtype table extension, if there is only an attribute or 2 specific to membership. In my another subtype, I have 20-30 columns that are unique to THAT subtype. The difference are not subtle, and it would be ludicrous to have one PRODUCT table with 60 columns when the vast majority wouldn't apply to many products. 17 hours ago, gizmola said: If it's only going to be "duration" and "can_be_renewed" flag, I don't think I'd bother. I'd just stick a couple of extra columns in Product. No, it's much much more. But legitimate questions! 🙂 Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577926 Share on other sites More sharing options...
gizmola Posted May 13, 2020 Share Posted May 13, 2020 13 minutes ago, SaranacLake said: Oh? For as long as I have been working with databases, that concept has existed. Are you saying it not formal E.F. Codd stuff, or what? Correct. A subtype is an object oriented programming concept. A subtype is a derived/child class. Relational databases only deal with relational algebra. There is no SQL for inheritance. 1 hour ago, SaranacLake said: Why? The pricing for each subtype is unique. When you are designing the tables, you have to ask yourself the question of what the attribute is a property of. Is it a property of a Product (yes!) or only a property of a membership. If all your products that aren't memberships are going to be free.. well then ok 1 hour ago, SaranacLake said: For the Order, yes. For the Subscription, no. The Order "stream" links a MEMBER to an ORDER and ultimately a PRODUCT. The Order "stream" does not specify which PRODUCT_VERSION the MEMBER subscribed to, nor anything about the SUBSCRIPTION. That is the catch. I don't know what you mean by order stream. If we can't look at an ERD there are going to be misunderstandings. 1 hour ago, SaranacLake said: Was there a place I was not normalizing based on the little I told you? I don't know if you are denormalizing or not. I was referring to relational patterns, and I would consider your proposition to make a subtype table for some reason, when you aren't making subtype tables for all the "product types" would be an anti -pattern. But again, I don't know what your ERD looks like. I do know when you are designing something that will require procedural code just to understand how and when queries would be formulated. Pricing seems to be the same sticking point. You seem to think that pricing needs to be in a product subtype table, and I know that isn't the case. But it's your system so --- 1 hour ago, SaranacLake said: What part don't you like as far as naming goes? You named your product subtype table member_plan. It should be named product_membership (again assuming that 'membership' is your subtype of product. 1 hour ago, SaranacLake said: Columns that apply to *all* subtypes would go into the supertype, products. Agree? Exactly. And that includes pricing, because pricing, even versioned pricing, is an attribute of all products, not just membership products. 1 hour ago, SaranacLake said: The difference are not subtle, and it would be ludicrous to have one PRODUCT table with 60 columns when the vast majority wouldn't apply to many products. Well, again, you might have a good argument for this, but I doubt there would or should be that many columns. I've learned over the years to err on the side of having too few columns rather than too many. You can always add a column, but it's beneficial to question each and every byte you use. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577932 Share on other sites More sharing options...
SaranacLake Posted May 13, 2020 Author Share Posted May 13, 2020 17 minutes ago, gizmola said: Correct. A subtype is an object oriented programming concept. A subtype is a derived/child class. Relational databases only deal with relational algebra. There is no SQL for inheritance. Okay, I didn't know that. 17 minutes ago, gizmola said: When you are designing the tables, you have to ask yourself the question of what the attribute is a property of. Is it a property of a Product (yes!) or only a property of a membership. If all your products that aren't memberships are going to be free.. well then ok For simplicity of conversation, I have a PRODUCT supertype and 3 subtypes. Each Substype has a SUBTYPE_VERSION table (1 to m) which stores each versions of a product. I put the "unit_price" in the SUBTYPE_VERSION tables because there are many "unit_prices" While I see that "unit_price" ultimately ties back to PRODUCT, I would argue it ties more closely to SUBTYPE_VERSION. (Windows95 cost $100, Windows XP cost $125, Windows 7 costs $150 and Windows 10 costs $200.) Now I could create a PRODUCT_VERSION table to handle the multiple prices, but that just adds an extra table and it makes more sense to say Widget v2.0 = $75. So I think my design is okay, and there are lots of other things in SUBTYPE_VERSION that justify that design as well. 17 minutes ago, gizmola said: I don't know if you are denormalizing or not. I was referring to relational patterns, and I would consider your proposition to make a subtype table for some reason, when you aren't making subtype tables for all the "product types" would be an anti -pattern. But again, I don't know what your ERD looks like. I have: PRODUCT ===> SUBTYPE-A ===> SUBTYPE-A_VERSION PRODUCT ===> SUBTYPE-B ===> SUBTYPE-B_VERSION PRODUCT ===> SUBTYPE-C ===> SUBTYPE-C_VERSION Total symmetry. 17 minutes ago, gizmola said: I do know when you are designing something that will require procedural code just to understand how and when queries would be formulated. Pricing seems to be the same sticking point. You seem to think that pricing needs to be in a product subtype table, and I know that isn't the case. But it's your system so --- But see my earlier comments... Why do in 4 tables what you can do in 3 tables? 17 minutes ago, gizmola said: You named your product subtype table member_plan. It should be named product_membership (again assuming that 'membership' is your subtype of product. Verbose even for me, but I can see the benefit. 17 minutes ago, gizmola said: Exactly. And that includes pricing, because pricing, even versioned pricing, is an attribute of all products, not just membership products. See earlier comment. 17 minutes ago, gizmola said: Well, again, you might have a good argument for this, but I doubt there would or should be that many columns. I've learned over the years to err on the side of having too few columns rather than too many. You can always add a column, but it's beneficial to question each and every byte you use. One of my subtypes is ONLINE_BOOK. Every component of a book is in that table. Does an "Author" or "page_count" or "subtitle" relate to the subtype TSHIRTS? Of course not. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577934 Share on other sites More sharing options...
gizmola Posted May 13, 2020 Share Posted May 13, 2020 17 hours ago, SaranacLake said: While I see that "unit_price" ultimately ties back to PRODUCT, I would argue it ties more closely to SUBTYPE_VERSION. (Windows95 cost $100, Windows XP cost $125, Windows 7 costs $150 and Windows 10 costs $200.) This is not how to do this sort of thing in my opinion. You are simply talking about related products, not versions. You can either handle that as a hierarchy (not as flexible) or a full A -> B relationship table. This is typically what is done in enterprise systems that are sold for large commercial enterprises, or in an ERP. Product >--- ProductProduct >-- Product V | RelationshipType RelationshipType examples: Package Item Sequel/Replacement Part ProductProduct basic structure ----------------------------------- id (pk) parent_product_id (fk Product) child_product_id (fk Product) relationshipType_id (fk Relationship Type) created_on 17 hours ago, SaranacLake said: I have: PRODUCT ===> SUBTYPE-A ===> SUBTYPE-A_VERSION PRODUCT ===> SUBTYPE-B ===> SUBTYPE-B_VERSION PRODUCT ===> SUBTYPE-C ===> SUBTYPE-C_VERSION I don't know what you mean by this. Are you saying you have 3 product subtypes and each one is named "foo", and then you have "foo_version"? Or are you just saying you have a subtype column in Product, and this has the word "foo" in it for "foo" items? 17 hours ago, SaranacLake said: But see my earlier comments... Why do in 4 tables what you can do in 3 tables? No idea what you are talking about. You seem to have a proliferation of tables, and I'm arguing that you can do the same things with a more generic approach that would actually be fewer tables. If you insist on putting pricing in each individual subtype table, you are creating more tables. That's not better or fewer tables. The other thing you are glossing over is the procedural cost of generating a product catalog, or storing data in that catalog. Rather than having one join that gets a product and its pricing, in your system, there has to be separate queries at minimum for each product subtype. That is not good. Anytime you have to fall down to procedural code to do something, you have not made the system database driven. It's going to be duplicative logic with a proliferation of code to do just about anything you need. Again, just my 2 cents, but I'm feeling at this point we are retreading the same ground for a bit, and the back in forth has a lot to do with your lack of presentation of an ERD. I would never go into a development project without an ERD, not just because it's a picture, but also because it will aid you with DDL generation, and improvements. There is nothing more painful from a development standpoint than having to fix a database that you come to find is incorrect, especially if you already have production data in it. I don't know if you don't have an ERD, and that's why we haven't seen one, but if this is a serious business project for you, then you really should. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577961 Share on other sites More sharing options...
SaranacLake Posted May 14, 2020 Author Share Posted May 14, 2020 5 hours ago, gizmola said: This is not how to do this sort of thing in my opinion. You are simply talking about related products, not versions. Pricing relates to the "version" of a Product. You cannot change the price once a Version is out. That is a business rule. So logically, the "unit_price" is tied much more tightly to the Product > Product_SubType > Product_SubType_Version than to the generic Product. (And it would require a lot more code to enforce that.) If you could change the "unit_price" anytime you wanted, then I agree that you would tie things to the Product table, but to my point, then that requires adding a Product > ProductPricing table to handle the many unit prices. 5 hours ago, gizmola said: No idea what you are talking about. You seem to have a proliferation of tables, and I'm arguing that you can do the same things with a more generic approach that would actually be fewer tables. If you insist on putting pricing in each individual subtype table, you are creating more tables. That's not better or fewer tables. I just addressed this. 5 hours ago, gizmola said: The other thing you are glossing over is the procedural cost of generating a product catalog, or storing data in that catalog. Rather than having one join that gets a product and its pricing, in your system, there has to be separate queries at minimum for each product subtype. That is not good. Anytime you have to fall down to procedural code to do something, you have not made the system database driven. It's going to be duplicative logic with a proliferation of code to do just about anything you need. I addressed this last night... Why would I lump together Subscriptions, an Online_Book and Tshirts into one table, when only 2-3 columns would be shared? Are you telling me that you'd just have one Product table for such disparate things? 5 hours ago, gizmola said: Again, just my 2 cents, but I'm feeling at this point we are retreading the same ground for a bit, and the back in forth has a lot to do with your lack of presentation of an ERD. I would never go into a development project without an ERD, not just because it's a picture, but also because it will aid you with DDL generation, and improvements. There is nothing more painful from a development standpoint than having to fix a database that you come to find is incorrect, especially if you already have production data in it. I don't know if you don't have an ERD, and that's why we haven't seen one, but if this is a serious business project for you, then you really should. I have ERD's that fill up my entire place. I'm just not willing to reveal the design for my entire - which is what the database is - here. 😉 And because my ecommerce module is complex, I didn't want to throw up 20+ tables here. (Plus, very ironic to Barand's earlier grumblings, there has been a long history where I take an hour typing up my design and code and business rules, post things, and then people ignore it because they perceive it as too complicated. So beyond protecting my design, I didn't think anyone would read what I typed had I shown more.) I can try my best to answer questions posed to me, and I welcome people challenging my thinking/design. But I'm sure you can appreciate why I'm not ready to post a design I have spent months working on. I see your point about getting siloed, but alas, I am storing and selling disparate things and that is why God created supertypes/subtypes. Plus you have to also consider that I am NOT Amazon.com, and in a year from now i will NOT be selling 2,000 different categories of Products, so I think your concerns wouldn't apply in my case. If I was Amazon, then yes, I'd need a more "enterprise" design. Hope that helps answer your questions. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1577972 Share on other sites More sharing options...
gizmola Posted May 16, 2020 Share Posted May 16, 2020 On 5/13/2020 at 8:22 PM, SaranacLake said: Pricing relates to the "version" of a Product. You cannot change the price once a Version is out. That is a business rule. So logically, the "unit_price" is tied much more tightly to the Product > Product_SubType > Product_SubType_Version than to the generic Product. (And it would require a lot more code to enforce that.) If you could change the "unit_price" anytime you wanted, then I agree that you would tie things to the Product table, but to my point, then that requires adding a Product > ProductPricing table to handle the many unit prices. Yes exactly. You need a productPrice table with a fromDate/toDate pair of dates. I don't really understand your point about not changing prices. Most businesses want to be able to change the price of something whenever it suits them. You require a link to the ProductPrice table for any lineitems in an order, so you have a link back to the historic record of the price. This is an attribute of a Product plain and simple. It doesn't matter what type of product it is. Pricing doesn't belong down in a subtype table, but I'm repeating myself. On 5/13/2020 at 8:22 PM, SaranacLake said: Pricing relates to the "version" of a Product. You cannot change the price once a Version is out. That is a business rule. So logically, the "unit_price" is tied much more tightly to the Product > Product_SubType > Product_SubType_Version than to the generic Product. (And it would require a lot more code to enforce that.) Again, this isn't logical. But I also explained that a product is a product is a product. There is no need for "versions" of a product when all you are trying to do is establish a relationship hierarchy between products. This is done in systems all the time, and I showed the design pattern that is effective for dealing with that. On 5/13/2020 at 8:22 PM, SaranacLake said: Are you telling me that you'd just have one Product table for such disparate things? Yes, but I've only done about 100 commerce, distribution, manufacturing and ERP projects, so what do I know On 5/13/2020 at 8:22 PM, SaranacLake said: Plus you have to also consider that I am NOT Amazon.com, and in a year from now i will NOT be selling 2,000 different categories of Products, so I think your concerns wouldn't apply in my case. If I was Amazon, then yes, I'd need a more "enterprise" design. I'm very aware of that, but rather than have a simple straightforward and relationally sound design, you have meandered towards one that is procedural. All I can do is tell you when things look wonky and prognosticate the cost of your decisions in decreased flexibility and reliability, compounded by increased development time to support the design at its most basic level. On 5/13/2020 at 8:22 PM, SaranacLake said: I see your point about getting siloed, but alas, I am storing and selling disparate things and that is why God created supertypes/subtypes. Once again, OOP is a useful programming language feature in many cases, but is not a feature of relational databases. On 5/13/2020 at 8:22 PM, SaranacLake said: I have ERD's that fill up my entire place. I'm just not willing to reveal the design for my entire - which is what the database is - here. Nobody asked for that. With that said, selling memberships is a common feature of a gazillion businesses, so it's highly doubtful you have invented a trade secret. The basic relationships with the keys and relations of tables for the ones you are talking about is all anyone expected. A lot of the projects I've worked on have lots of tables, and typically you can only look at a small number at a time. I have worked for companies that had plotters where I would print out a fullsize view of the entire ERD or a large portion thereof a few times in my career, but usually I'm sticking to something that can fit on a page or a large .png that can be viewed on screen. This is what people wanted from you, although it seems like a moot point at this juncture. There are many ways to solve the same problem. You seem satisfied with what you have, so I'm not going to beat a dead horse. I do think that you should be moving on to the construction phase of your project at this point. I know an ERD tool is a valuable time saver, but many of the ones I've used over the years cost thousands of dollars and were paid for by the companies I was working for at the time. I personally own a commercial product I use in my consulting practice, but if I didn't and I wanted something effective and no cost, I'd use MySQL workbench. If you have all these designs on paper, you will be much better off moving them to workbench, and once complete, using it to generate the DDL to create your tables. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578090 Share on other sites More sharing options...
SaranacLake Posted May 17, 2020 Author Share Posted May 17, 2020 (edited) @gizmola, Genuinely trying to understand your suggestions... 3 hours ago, gizmola said: Yes exactly. You need a productPrice table with a fromDate/toDate pair of dates. I don't really understand your point about not changing prices. Most businesses want to be able to change the price of something whenever it suits them. Because customers might not appreciate if I charge person-A $50 and person-B $60 for the same thing. That, and I'm not greedy. If there is a new product version, with more value, then I will increase the price, otherwise, it remains the same price for the same offering. Quote This is an attribute of a Product plain and simple. It doesn't matter what type of product it is. Pricing doesn't belong down in a subtype table, but I'm repeating myself. Since I have 3 subtype tables that can also handle the "unit_price" I figured it is easier to put it there than create a Product > ProductPriceHistory table (4th table) ad then have to write code so that a "unit_price" is specific to a product version, because that is my business rule. And since Subscriptions and Books are very Release/Version/Edition driven, that seems like a good design. (If this was for a gas company or commodities, then I would likely use your approach.) Quote Again, this isn't logical. But I also explained that a product is a product is a product. There is no need for "versions" of a product when all you are trying to do is establish a relationship hierarchy between products. This is done in systems all the time, and I showed the design pattern that is effective for dealing with that. I have product versions because each version is different - not just in price, but features. Is Windows 7 the same as Windows 10? How would you know the differences between Windows 7 and Windows 10 if you didn't store the differences? I keep track of product changes/releases by having different product versions. So a product is NOT a product when you keep track of versions. You seem to think I am creating product versions to keep track of the price and that is NOT the purpose of "versions"! Please help me understand why I do not need to keep track of product versions... Quote I'm very aware of that, but rather than have a simple straightforward and relationally sound design, you have meandered towards one that is procedural. What do you mean by a "procedural" database design? And please help me understand why you feel there is only a need for one "God" Product table when I am selling very different products. With your design, I would have one Product table, and say 65 columns, where the first 5 are common among all products, the next 20 columns describe Subscriptions, the next 20 columns describe Online Books and the last 20 columns describe T-Shirts. So now when I query the Product table, I have to sift through all those columns and records when I only need a fraction of the data - very resource intensive. How does that help me out? Let me also reiterate that I am storing each Online Book in the ProductOnlineBook subtype table, and one of my books is 1,000 pages. So why put that in with records related to T-shirts?? And, how does breaking things into 3 subtypes create a problem for my Product Catalog? (When you visit the Product Catalog in my site, it is blank, just like any other e-commerce site, you choose a Category, AND THEN I query that Product/Sub-Type combination and display only those items. It's not like the ears catalog where I am displaying everything all at once - if I was then you have a point.) Quote All I can do is tell you when things look wonky and prognosticate the cost of your decisions in decreased flexibility and reliability, compounded by increased development time to support the design at its most basic level. Right, and I am genuinely asking you to help me better understand why my design is so flawed. You haven't answered the questions I am re-asking above, and maybe answers to those questions will help me to "see the light"! (Or maybe I'm not so wrong after all?!) 😉 Quote Once again, OOP is a useful programming language feature in many cases, but is not a feature of relational databases. I dunno, I learned about super-types/sub-types in my relational database classes in college, so I don't think that's a foreign concept. And not having my "bible" database book in front of me, I cannot comment how this falls into E.F. Codd stuff, but it is hardly an "Object-Oriented Database" design?! Quote Nobody asked for that. With that said, selling memberships is a common feature of a gazillion businesses, so it's highly doubtful you have invented a trade secret. The basic relationships with the keys and relations of tables for the ones you are talking about is all anyone expected. A lot of the projects I've worked on have lots of tables, and typically you can only look at a small number at a time. Maybe so, but hopefully you can understand why I am not too quick to start typing up about 10-15 tables in a public forum. PLUS, as I said before, 9 times out of 10 when I do that, nobody even responds, so I save myself the typing. Quote There are many ways to solve the same problem. You seem satisfied with what you have, so I'm not going to beat a dead horse. Yes, I do feel like I have a nearly finished solution - and a good one. But if you can help me understand what you are saying better, then maybe I will change my mind. I am always eager to learn - especially from someone who has done as much as you say. Quote I do think that you should be moving on to the construction phase of your project at this point. I have one last part to figure out, and hopefully it will be done before I go to bed. Then I need to take a day or two reading the MySQL manual to refresh my memory on things, and then next week - pandemic worries aside - I will FINALLY get to coding the e-commerce portion of my website, and hopefully get this thing done in the next month or two. Quote I know an ERD tool is a valuable time saver, but many of the ones I've used over the years cost thousands of dollars and were paid for by the companies I was working for at the time. I personally own a commercial product I use in my consulting practice, but if I didn't and I wanted something effective and no cost, I'd use MySQL workbench. If you have all these designs on paper, you will be much better off moving them to workbench, and once complete, using it to generate the DDL to create your tables. Since I have everything on paper in ERD format, i will likely just use phpMyAdmin to create the tables - or if I feel ballzy, i will write the SQL DDL and paste it into phpMyAdmin to create things. I need to learn MySQL Workbench, but I don't think it's a priority until after I go live - it'll take me weeks to learn and master it, and that is time wasted right now. Edited May 17, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578097 Share on other sites More sharing options...
gizmola Posted May 18, 2020 Share Posted May 18, 2020 On 5/16/2020 at 7:29 PM, SaranacLake said: Because customers might not appreciate if I charge person-A $50 and person-B $60 for the same thing. That, and I'm not greedy. If there is a new product version, with more value, then I will increase the price, otherwise, it remains the same price for the same offering. There is a difference between "I don't plan on doing that anytime soon" and "it's impossible to do that, because I designed the database to make it impossible". I have never seen a commerce system where a product could only have one price ever. On 5/16/2020 at 7:29 PM, SaranacLake said: Is Windows 7 the same as Windows 10? No. They are 2 different products. What does version have to do with this? They are not the same product with 2 different versions. They are 2 different projects that have one or more relationships between them. You establish that relationship using the relationship table. Of course, in your system I'm somewhat baffled as to why this is important. On 5/16/2020 at 7:29 PM, SaranacLake said: And please help me understand why you feel there is only a need for one "God" Product table when I am selling very different products. Again, if you want to use subtype tables for your product table, as you planned, that is fine. The only things that should go into those tables, are the attributes that are purely specific to their type. Product price is not one of those things, since all Products have a price. That is the rigor that needs to go into determining what is an attribute of a product, and what is an attribute of a product subtype. Now here is the cost: When you do anything with a product, that requires the information in the subtype, you must hydrate the value by joining from product to the specific product subtype table. That is procedural code, vs. a straight relationship between tables and where criteria to limit or filter. As I mentioned, there are ORM's that will automagically take care of this hydration process for you, if you adhere to naming standards and set everything up correctly, but in your case, you will most likely be writing your own customized queries to do this. On 5/16/2020 at 7:29 PM, SaranacLake said: Let me also reiterate that I am storing each Online Book in the ProductOnlineBook subtype table, and one of my books is 1,000 pages. So why put that in with records related to T-shirts?? Why? The book structure should be separate, and should work for an online book whether or not you are selling it. The fact that it's a digital good you will deliver upon sale or membership is something you should engineer separately. On 5/16/2020 at 7:29 PM, SaranacLake said: I dunno, I learned about super-types/sub-types in my relational database classes in college, so I don't think that's a foreign concept. And not having my "bible" database book in front of me, I cannot comment how this falls into E.F. Codd stuff, but it is hardly an "Object-Oriented Database" design?! Relational databases are built upon relational algebra and set theory. You are confusing the persistence of data that might support oop inheritance with actual relational (and SQL) support for it. If there was support for it, there would be SQL syntax to indicate that. What SQL syntax are you aware of provides you subclassing of a table? There isn't even support for hierarchical SELECT statements. Oracle has Connect By, which is something specific to the Oracle RDBMS, but doesn't exist in MySQL. People have figured out design patterns and workarounds to deal with hierarchical data. By making these seperate type tables and relating them (using a defining relationship) to the Product table, you are utilizing a workaround that will allow you to sensibly persist data, but adds complexity and development cost. I'm not arguing not to do it, but you should at least be aware that it isn't a standard pattern that works with normalization. I have used subtypes myself, so I'm not intrinsically against them, nor am I inexperienced with the practicality of using them. On 5/16/2020 at 7:29 PM, SaranacLake said: I need to learn MySQL Workbench, but I don't think it's a priority until after I go live - it'll take me weeks to learn and master it, and that is time wasted right now. There is no way it would take weeks. I doubt it would take a day. You install it and you start making tables, adding attributes (ie. moving your paper diagram to the model). You relate the tables by connecting them using the tool. When things look right, you hit a button and the DDL spits out. If it's wrong, you fix it in the model and hit the button again. These tools also have useful quality control features like domains. A domain is a datatype you name and configure. So for example, let's say you have a standard description column you use in many tables. You start out with the idea that it should be a varchar(40). So perhaps you have 20 tables with descriptions in them. If you set up a domain, perhaps named "stdDesc" and set each description column to be a "stdDesc" you can change all those attributes in one go, by changing the definition of the "stdDesc" domain. If not, then you have to go change description in 20 tables. Making domains for things like keys, timestamps and other common types will conform the database to standards, so that when you do write your code, you can be sure that quality standards are baked in from the beginning. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578164 Share on other sites More sharing options...
SaranacLake Posted May 19, 2020 Author Share Posted May 19, 2020 6 hours ago, gizmola said: There is a difference between "I don't plan on doing that anytime soon" and "it's impossible to do that, because I designed the database to make it impossible". I have never seen a commerce system where a product could only have one price ever. Well, I don't have that. I do have a business rule that says that the price for a given Product "version" is set. Decide that v3 is underpriced? Then create v4 and set the price to whatever. It's not a big deal. I chose this business design, because my main offering is subscription, and why should person-A pay $50 for v2 and yet person-B pays $60 for v2 the next day? Now you may say, "But that happens all of the time in real life - look at the price of gas?! But I am not selling gas, and I'm not so desperate that I have to screw with the price of things on a daily basis. (I sooo hate retailers where *every* price is not the real price, because everything is always on sale - worse than going to a car dealership!) So I have quite a bit of flexibility to change price, I have just chosen to have one price per version. 6 hours ago, gizmola said: No. They are 2 different products. What does version have to do with this? They are not the same product with 2 different versions. They are 2 different projects that have one or more relationships between them. You establish that relationship using the relationship table. Of course, in your system I'm somewhat baffled as to why this is important. Seriously? I know you are smarter, and able to think more abstractly, than that! Windows is a "product". A single product. (macOS is a different "product".) Every so often, Microsoft comes up with a newer version of the Windows "product", and in the Englisg language most people refer to these difference as a "version". (e.g. Hey Bob, what version of Windows do you guys run at work?) If I offer a Subscription, it is one PRODUCT, but the initial VERSION (i.e. v1.0) might just include: News and Articles. However, later on, a Subscription, which is still conceptually the same PRODUCT, might offer more features, including: News, Articles, Podcasts, and Exclusive Interviews, and so the VERSION would be incremented to v2.0 Where is the disconnect? 6 hours ago, gizmola said: Again, if you want to use subtype tables for your product table, as you planned, that is fine. The only things that should go into those tables, are the attributes that are purely specific to their type. Product price is not one of those things, since all Products have a price. That is the rigor that needs to go into determining what is an attribute of a product, and what is an attribute of a product subtype. I understand that, BUT... If I associated the "unit_price" with the PRODUCT - which from a pure modeling standpoint is correct, then I have to create a PRODUCT_PRICE_HISTORY table because there will be hundreds if not thousands of prices for each PRODUCT over time, so now I need an extra table in addition to my 3 PRODUCT_<subtype>_VERSION tables which currently hold the SubType's "unit_price". AND, if I want to keep my business rule of "only one price per Product version", then I have to create triggers/stored procedures, or PHP code, to ensure that this business rule is honored. So, yeah, if you want to split hairs, putting the "unit_price" in my PRODUCT_<subtype>_VERSION isn't really correct from a modeling standpoint, HOWEVER, it allows me to avoid a 4th table, and all of the logic I need to ensure my business rule. (And on a side note, you can argue technical things all day, but it's not fair to tell me that my business rules are wrong, because that is rarely the case. If a business wants to give out a free toaster t=with each new checking account, that is their own damn business! Just sayin...) So now we come down to PRAGMATICS... From a purist standpoint, your approach is better/right, but from a practical standpoint, I think my approach is more than acceptable for a non-mazon.com take over the Universe solution. See? 6 hours ago, gizmola said: Now here is the cost: When you do anything with a product, that requires the information in the subtype, you must hydrate the value by joining from product to the specific product subtype table. That is procedural code, vs. a straight relationship between tables and where criteria to limit or filter. As I mentioned, there are ORM's that will automagically take care of this hydration process for you, if you adhere to naming standards and set everything up correctly, but in your case, you will most likely be writing your own customized queries to do this. I found a design off of StackExchange that mimics an Oracle Check constraint using tables that would completely enforce referential integrity using subtypes. There is no coding involved! And I don't see what you mean by "hydrate"? If I need data in a Product SubType, I run ONE query over two tables and I'm done. (How is that any different than running a query to say get ORDER and ORDER_DETAILS data in one query from a one-to-many? It isn't, except my query is two tables and a one-to-one relationship. 6 hours ago, gizmola said: Why? The book structure should be separate, and should work for an online book whether or not you are selling it. The fact that it's a digital good you will deliver upon sale or membership is something you should engineer separately. Where else would you store the contents for an Online Book other than in the ONLINE_BOOK table - and related ONLINE_BOOK_CONTENTS table?? 6 hours ago, gizmola said: Relational databases are built upon relational algebra and set theory. You are confusing the persistence of data that might support oop inheritance with actual relational (and SQL) support for it. If there was support for it, there would be SQL syntax to indicate that. What SQL syntax are you aware of provides you subclassing of a table? Fair enough. But is it a mortal sin to choose the design I have chosen? In this case, no. 6 hours ago, gizmola said: There isn't even support for hierarchical SELECT statements. Oracle has Connect By, which is something specific to the Oracle RDBMS, but doesn't exist in MySQL. People have figured out design patterns and workarounds to deal with hierarchical data. By making these seperate type tables and relating them (using a defining relationship) to the Product table, you are utilizing a workaround that will allow you to sensibly persist data, but adds complexity and development cost. I responded to this above. All it does is make my queries *slightly* more complex. Or am I missing something? 6 hours ago, gizmola said: I'm not arguing not to do it, but you should at least be aware that it isn't a standard pattern that works with normalization. I have used subtypes myself, so I'm not intrinsically against them, nor am I inexperienced with the practicality of using them. So in my case, where I have TONS of columns that simply do NOT relate to other Products, it makes much more sense to split things than have a cluster-f table. At least that is my stance... 6 hours ago, gizmola said: There is no way it would take weeks. I doubt it would take a day. You install it and you start making tables, adding attributes (ie. moving your paper diagram to the model). You relate the tables by connecting them using the tool. When things look right, you hit a button and the DDL spits out. If it's wrong, you fix it in the model and hit the button again. These tools also have useful quality control features like domains. A domain is a datatype you name and configure. So for example, let's say you have a standard description column you use in many tables. You start out with the idea that it should be a varchar(40). So perhaps you have 20 tables with descriptions in them. If you set up a domain, perhaps named "stdDesc" and set each description column to be a "stdDesc" you can change all those attributes in one go, by changing the definition of the "stdDesc" domain. If not, then you have to go change description in 20 tables. Making domains for things like keys, timestamps and other common types will conform the database to standards, so that when you do write your code, you can be sure that quality standards are baked in from the beginning. Everything takes me l-o-n-g-e-r! 😉 My brain is overloaded, so Workbench can wait. I have my blueprint - even though you don't like it - and while phpMyAdmin is not glamorous, it works. Plus, I will likely be actually writing my own DDL's and DML's so i actually learn/relearn something. (In everything i do, I am trying to learn and think independently, and not just copy and paste like so many people.) Plus, if i write my own SQL, I can control little things like the names for my indexes and constraints, and I'm anal-retentive like that! But from a data modeling standpoint, next time i wanna design v2.0 of my system using a data modeling tool, because having to redrawn my ERDs 100 times because on the last line my pen slipped and I had to redraw an entire page over at 3am is B.S.!! 😠 Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578170 Share on other sites More sharing options...
gizmola Posted May 19, 2020 Share Posted May 19, 2020 1 hour ago, SaranacLake said: Windows is a "product". A single product. (macOS is a different "product".) This is in no way true. A family of products? Sure. If you want to argue that Windows 10 has "versions" for internal purposes (patches etc.) that is true, but they don't sell that. They sell Windows 10 vs. Windows 8 vs Windows 7. 1 hour ago, SaranacLake said: If I offer a Subscription, it is one PRODUCT, but the initial VERSION (i.e. v1.0) might just include: News and Articles. Step back from your convictions for a second and imagine you don't have this versioning scheme. Can you sell these as 2 separate products both named Subscription? Of course -- the name is going to be a varchar. You might differentiate them by status (active vs. inactive) and a date. They will each have a different id. Any bill of materials/permissions scheme you want to link to those can be done in relationship to the product. The fact that you have a linked "version" table does absolutely nothing that you can't do building a relation directly to product. I am not going to continue to argue about it, because it really only matters if you are interested in understanding how to implement it, and it's pretty clear you aren't. I don't have any skin in the game, so I'm not going to continue to beat a dead horse. 1 hour ago, SaranacLake said: If I associated the "unit_price" with the PRODUCT - which from a pure modeling standpoint is correct, then I have to create a PRODUCT_PRICE_HISTORY table because there will be hundreds if not thousands of prices for each PRODUCT over time, so now I need an extra table in addition to my 3 PRODUCT_<subtype>_VERSION tables which currently hold the SubType's "unit_price". You were just arguing that your subscription product will rarely change price (perhaps once a year). In a decade you have 10 prices. Also, you don't have a history table. You have a price table period and all prices are good for a date range: ProductPrice ------------ id (pk) product_id (fk) price decimal(7,2) fromDate timestamp toDate timestamp When you sell something you have to retain a link to the productPrice id in the lineitem detail for the sale, but otherwise there's no complexity to this. At any time you should be able to find which price to display because NOW() is between fromDate and toDate 2 hours ago, SaranacLake said: I found a design off of StackExchange that mimics an Oracle Check constraint using tables that would completely enforce referential integrity using subtypes. There is no coding involved! MySQL already has referential integrity, assuming you use the InnoDB engine. With a defining relationship, you will already have declarative referential integrity, so I'm not sure what you are referring to. Hydration is a term frequently used with ORM's. It's basically the way with which an object is filled with data. This hydration can be automated to include relationships either lazily or at load time. If you configured the ORM correctly, it figures out how to resolve the subtype and add all the attributes from the subtype table, essentially merging them into one product. It can do this for one object or for a query that involves some mixture of products. Now with your scheme, it won't be possible to get even a price for a subscription product without a special query that you've coded up specifically for subscriptions. I'm not sure that there is going to be a benefit whatsoever to having a product table, or more likely you'll just have a lot of repetitive slightly different code with different queries by product type. Maybe you will manage to make this code clean and testable and discreet, but I don't see any way it will be elegant and database driven with a lot of reusable generalized routines. 2 hours ago, SaranacLake said: I have my blueprint - even though you don't like it - and while phpMyAdmin is not glamorous, it works. Plus, I will likely be actually writing my own DDL's and DML's so i actually learn/relearn something. (In everything i do, I am trying to learn and think independently, and not just copy and paste like so many people.) Again I can only provide advice, even if you choose to ignore it. If you hand code the SQL you will make many mistakes, some which you probably won't even realize. Hopefully you will at very least make sure that all your relationships are correct, your integer auto_increment keys are unsigned, and you also use the smallest keys that make sense. Don't just make all your keys bigint. Good luck with the project Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578178 Share on other sites More sharing options...
SaranacLake Posted May 20, 2020 Author Share Posted May 20, 2020 (edited) 19 hours ago, gizmola said: This is in no way true. A family of products? Sure. If you want to argue that Windows 10 has "versions" for internal purposes (patches etc.) that is true, but they don't sell that. They sell Windows 10 vs. Windows 8 vs Windows 7. Clearly how we conceive a "Product" is different. If you can summarize and articulate why later on I will regret using a Super/SubType approach, AND why not having all pricing history tied directly to the Product supertype, then I'd love to better understand your design recommendations. But it is not clear to me where the gaping hole in my design is? is it an integrity thing? Is it a coding effort thing? Will I become siloed? Pigeon-holed? Sorry, but the end "you're gonna regret this because _____" hasn't jumped out at me. (I might consider changing how I do pricing history, but likely will leave things because I have reached "analysis paralysis" and I need to get this website done and go live in the next month or so, or I should just quit and go open a bakery?! And if "I" am saying "it is good enough" then that likely means I have reached some limit of the Universe!!) Quote Step back from your convictions for a second and imagine you don't have this versioning scheme. Can you sell these as 2 separate products both named Subscription? Of course -- the name is going to be a varchar. You might differentiate them by status (active vs. inactive) and a date. They will each have a different id. Any bill of materials/permissions scheme you want to link to those can be done in relationship to the product. The fact that you have a linked "version" table does absolutely nothing that you can't do building a relation directly to product. It's the very fact that the name does NOT change that I choose to call them one Product with many Versions. If I have a "Platinum" subscription, to the customer it will always be a "Platinum" subscription - just over time which features it has will change. If I was selling Windows 7 and Windows 10, I could see treating each as a separate "Product" because of their name. But I am just selling a "Platinum" subscription, or "Teach Yourself PHP in 21 Days -- 1st Edition", "Teach Yourself PHP in 21 Days -- 2nd Edition", and so on. In either case, I think all of this is very subjective. I can see your way, but I can also see mine. Quote I am not going to continue to argue about it, because it really only matters if you are interested in understanding how to implement it, and it's pretty clear you aren't. I don't have any skin in the game, so I'm not going to continue to beat a dead horse. I thought we were having a discussion, or maybe a debate, and not an argument... If I wasn't interested, I wouldn't be asking for clarification. And just because I am defending my design decisions doesn't mean I am trashing your ideas. Yes, I am trying to genuinely understand why you say what you say, although I think it is clear this is one time where a lot is probably getting lost in the translation over the Internet. (If we were in the same conference room at the same whiteboard, I bet this would be easier.) Quote You were just arguing that your subscription product will rarely change price (perhaps once a year). I said once per Version, not once per year... Quote In a decade you have 10 prices. Also, you don't have a history table. You have a price table period and all prices are good for a date range: ProductPrice ------------ id (pk) product_id (fk) price decimal(7,2) fromDate timestamp toDate timestamp You're splitting hairs... I would call the table you describe above as a "Product Price History" table, because it shows a history of the evolution of a Product's price. And, FYI, I have exactly what you describe, I just chose to make it part of my Product_Version table, because I saw the Price as being tied more to the Version than the parent Product. I also have concurred a few times that your thoughts on pricing are probably more accurate than mine, but I also explained my justification. You make it sound like there is this enormous divide, and really I just see subtle differences, and nothing is "personal" from my end... Quote When you sell something you have to retain a link to the productPrice id in the lineitem detail for the sale, but otherwise there's no complexity to this. At any time you should be able to find which price to display because NOW() is between fromDate and toDate I understand how to model it, I just think it is more work than necessary. But clearly you think what I am doing is taboo. To me it's just a minor design tradeoff. Quote MySQL already has referential integrity, assuming you use the InnoDB engine. With a defining relationship, you will already have declarative referential integrity, so I'm not sure what you are referring to. And I'm not following what you were talking about either. You mention ORM a bunch, and I am not doing that, so there is likely the disconnect. And while I understand OOP somewhat, I don't use it because I don't really know it. So not only is my PHP not OOP, but my MySQL is definitely not object-oriented! Quote Hydration is a term frequently used with ORM's. It's basically the way with which an object is filled with data. This hydration can be automated to include relationships either lazily or at load time. If you configured the ORM correctly, it figures out how to resolve the subtype and add all the attributes from the subtype table, essentially merging them into one product. It can do this for one object or for a query that involves some mixture of products. Now with your scheme, it won't be possible to get even a price for a subscription product without a special query that you've coded up specifically for subscriptions. I'm not sure that there is going to be a benefit whatsoever to having a product table, or more likely you'll just have a lot of repetitive slightly different code with different queries by product type. Maybe you will manage to make this code clean and testable and discreet, but I don't see any way it will be elegant and database driven with a lot of reusable generalized routines. I have to punt here, because you're talking over my head in this area. All I can say is that I don't think any of that applies to my simple case. Quote Again I can only provide advice, even if you choose to ignore it. You're very black-and-white on some things... 😉 Quote If you hand code the SQL you will make many mistakes, some which you probably won't even realize. If I don't let Workbench generate my database for me then it will be riddled with mistakes? Really? I've known a lot of DBA over my career that would use a hammer on any tool that did things automatically over a human thinking on his own and doing things. Whether I use a tool like phpMyAdmin or Workbench or I handcode, I am confident I can build something that works. Quote Hopefully you will at very least make sure that all your relationships are correct, your integer auto_increment keys are unsigned, and you also use the smallest keys that make sense. Don't just make all your keys bigint. Yes dad. I may not be perfect, but I am methodical, and I pride my work, so I will do the best I can. Quote Good luck with the project Good thing you aren't my boss, because I have convinced you of nothing! Edited May 20, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578212 Share on other sites More sharing options...
gizmola Posted May 20, 2020 Share Posted May 20, 2020 5 hours ago, SaranacLake said: If you can summarize and articulate why later on I will regret using a Super/SubType approach, AND why not having all pricing history tied directly to the Product supertype, then I'd love to better understand your design recommendations. But it is not clear to me where the gaping hole in my design is? is it an integrity thing? Is it a coding effort thing? Will I become siloed? Pigeon-holed? Sorry, but the end "you're gonna regret this because _____" hasn't jumped out at me. (I might consider changing how I do pricing history, but likely will leave things because I have reached "analysis paralysis" and I need to get this website done and go live in the next month or so, or I should just quit and go open a bakery?! And if "I" am saying "it is good enough" then that likely means I have reached some limit of the Universe!!) Again, I haven't said you shouldn't use a subtype. I have used them myself. Ideally you would be using an ORM where a lot of the complexity is handled for you by the ORM. At this point, I don't see value in continuing to discuss it. At the end of the day, it's your business plan, your database and your code. So long as it works for you, and you can make the system work as you need it to, it doesn't matter that we likely aren't 100% on the same page. 5 hours ago, SaranacLake said: I thought we were having a discussion, or maybe a debate, and not an argument... If I wasn't interested, I wouldn't be asking for clarification. And just because I am defending my design decisions doesn't mean I am trashing your ideas. Yes, I am trying to genuinely understand why you say what you say, although I think it is clear this is one time where a lot is probably getting lost in the translation over the Internet. (If we were in the same conference room at the same whiteboard, I bet this would be easier.) I don't take it personally. It's free advice, and at the end of the day, I'm not hurt or offended if you decide not to take it, nor do I personalize it. There are many ways to accomplish things. 5 hours ago, SaranacLake said: I would call the table you describe above as a "Product Price History" table, because it shows a history of the evolution of a Product's price. And, FYI, I have exactly what you describe, I just chose to make it part of my Product_Version table, because I saw the Price as being tied more to the Version than the parent Product. You are welcome to think of it that way, even if I don't. To me the use of the word "history" has a connotation to it, that means it's a repository for things that are now obsolete. I don't look at it that way at all, even though prices do have an expiration. From my point of view, that table facilitates getting the price for any product that is valid as of this second. You need expired pricing so long as you need it, which might be until the system is decommissioned, assuming there is order history attached to a product price row, but that's just for audit and pricing purposes. I understand if you decided to put those fields down in the subtype table child, but that only returns us to more debate about your product/subtype/version model, which just doesn't compute for me. 5 hours ago, SaranacLake said: Whether I use a tool like phpMyAdmin or Workbench or I handcode, I am confident I can build something that works. I was just trying to give you the benefit of my experience. I have been the architect/project lead on a lot of projects in my career, and frequently I've been the database designer and pragmatically, the keeper of the schema, and responsible for generation of the DDL. Quality is very important, because mistakes small or large are costly, in terms of code redo, testing and refactoring. On any schema of moderate complexity, it's likely there will be some back and forth. Hand coding all the SQL is a pain, and it's hard to see the forest for the trees when you have a bunch of table definitions. Don't forget that you will want to have constraints, and when even a small change is made to a number of related tables, this can require a cascade of different tables and constraints (and possibly triggers or sprocs) that need to be recreated. Using a tool is vastly more efficient. Again, you have stated you don't plan to use one, and that is your choice, but you shouldn't take it as a personal attack that in my experience building a model in a tool is a boon to productivity, quality and helpful in the iterative process of refactoring to address things that were initially overlooked. It's also invaluable for communicating with developers in a team. Perhaps you don't care, because you don't see a time when you won't be a one person team. If anything I've understated the value of an ERD, and that's why I use one for my projects, large or small. 5 hours ago, SaranacLake said: Hopefully you will at very least make sure that all your relationships are correct, your integer auto_increment keys are unsigned, and you also use the smallest keys that make sense. Don't just make all your keys bigint. This was simply practical advice. I have worked on many a project where the originator neglected to make integer keys unsigned. Worse yet, they sometimes had foreign keys with a different definition from the primary key of the foreign table. I've also seen people create a small type table, as for example, one might create for the product_type values we've discussed, and make the primary key a bigint. Not too long ago I was working on a project for a service company that had many millions of transaction rows in one particular table, where there were about 14 "flags" or "status" columns. This was just one table, among many others, some of which had the same issue. The flag columns were typically booleans (0 false, 1 true). The status columns had a universe of 20 or less mutually exclusive values. With MySQL they could have used 1 byte tinyints, but the original developers made them all 4 byte integers. They were allocating 56 bytes on these columns per row, when they could have allocated 14. Compound this with a number of indexes on these columns, and the waste was in the terabytes. At that point there was nothing they could do about it, that didn't involve an expensive and painful maintenance process. That mistake had a very real cost to the company as the db was bloated, along with the indexes, which made all their expenditures on the rdbms infrastructure less effective. 6 hours ago, SaranacLake said: And I'm not following what you were talking about either. You mention ORM a bunch, and I am not doing that, so there is likely the disconnect. And while I understand OOP somewhat, I don't use it because I don't really know it. So not only is my PHP not OOP, but my MySQL is definitely not object-oriented You were discussing some pattern that would give you referential integrity. As for OOP, a subtype is an OOP concept, as I've commented previously. In OOP you make a class, and you can have other classes that inherit from the a parent class. A class that inherits from a parent class is a subtype. I would hope you plan to at least use component libraries. I have shared this video many times over the years (it's 8 years old now). PHP at the time was in bad shape, compared to its competitors. Then a group emerged from the symfony framework community that created the composer tool for managing component libraries and autoloading, and that completely changed the trajectory of the language. I highly recommend you watch this video, so you can understand the evolution to the current best practices for PHP development. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578222 Share on other sites More sharing options...
SaranacLake Posted May 22, 2020 Author Share Posted May 22, 2020 On 5/20/2020 at 5:06 AM, gizmola said: Again, I haven't said you shouldn't use a subtype. I have used them myself. Ideally you would be using an ORM where a lot of the complexity is handled for you by the ORM. At this point, I don't see value in continuing to discuss it. At the end of the day, it's your business plan, your database and your code. So long as it works for you, and you can make the system work as you need it to, it doesn't matter that we likely aren't 100% on the same page. Well, maybe after I really learn and master OPP I can look into ORM's, but that is over my head now. (Plus, are there even any open-source ORM's out there?) On 5/20/2020 at 5:06 AM, gizmola said: I don't take it personally. It's free advice, and at the end of the day, I'm not hurt or offended if you decide not to take it, nor do I personalize it. There are many ways to accomplish things. And I'm trying my best to build a competent solution. On 5/20/2020 at 5:06 AM, gizmola said: You are welcome to think of it that way, even if I don't. To me the use of the word "history" has a connotation to it, that means it's a repository for things that are now obsolete. I don't look at it that way at all, even though prices do have an expiration. So what would you call it? On 5/20/2020 at 5:06 AM, gizmola said: From my point of view, that table facilitates getting the price for any product that is valid as of this second. Right. Moreover, whether I have the "unit_price" in a dedicated table, or in my <subtype>_version tables, the price consists of Future, Now, and Past - which is another reason I have it tied to versions. Right now might be v15 and the price is $50. And in the past, the last version's price, v14, was $45. And the upcoming version, v16, will go up to $60. So I have a complete view of "now" and looking backwards and forwards. I just use the term "history" since to me that denotes a "timeline". On 5/20/2020 at 5:06 AM, gizmola said: You need expired pricing so long as you need it, which might be until the system is decommissioned, assuming there is order history attached to a product price row, but that's just for audit and pricing purposes. I understand if you decided to put those fields down in the subtype table child, but that only returns us to more debate about your product/subtype/version model, which just doesn't compute for me. Okay. On 5/20/2020 at 5:06 AM, gizmola said: I was just trying to give you the benefit of my experience. And I appreciate that immensely!!! 👍 On 5/20/2020 at 5:06 AM, gizmola said: I have been the architect/project lead on a lot of projects in my career, and frequently I've been the database designer and pragmatically, the keeper of the schema, and responsible for generation of the DDL. Quality is very important, because mistakes small or large are costly, in terms of code redo, testing and refactoring. One of the web pages I read on naming conventions stressed that "name things logically, because there is a good chance your DB will outlast several versions of your system codebase!" I believe it. On 5/20/2020 at 5:06 AM, gizmola said: On any schema of moderate complexity, it's likely there will be some back and forth. Hand coding all the SQL is a pain, and it's hard to see the forest for the trees when you have a bunch of table definitions. Well I want to step back on that, and also address something you seemed frustrated with in another thread... When I have time to learn to use it properly, i want to use Workbench so i have a dynamic place to create my ERD's - aka visual modeling. THAT is what I want from Workbench, and why logically I'm saving it for later. You see, 85% of my system is already built, including the database. (This e-commerce module is my last "scope creep" for v1.0!!) 🙂 I use phpMyAdmin because I know it, and it is quicker to use a GUI than handcode DDL's... (I realize that "real men" don't use GUI's, but whatever?!) And I am sure that not only does Workbench do visual modeling - which phpMyAdmin does not - but it probably does a lot of other more advanced things (e.g. ERD to schema and DDL's, reverse-engineering, etc.) So i don't see myself hand-coding everything as a rule of thumb. But on older versions of phpMyAdmin, you could NOT create and rename constraints and indexes, so you had to do it manually. Apparently when I updated MAMP last time, it now has a newer version of phpMyAdmin which DOES allow you to set and rename constraints and indexes, which is very cool! So why not use a GUI if you can? (I wasn't sure I could until today. See?) One more thing... I tend to be very "old-school", so I will likely always have hardcopies - and likely hand-drawn - ERD's of my system, because the database IS the system!! So while I am all for using tools, there is nothing like a 3-ring binder full of diagrams and ideas. Besides, you learn better when you write it down on paper! (A scientific fact!!) On 5/20/2020 at 5:06 AM, gizmola said: Don't forget that you will want to have constraints, and when even a small change is made to a number of related tables, this can require a cascade of different tables and constraints (and possibly triggers or sprocs) that need to be recreated. Using a tool is vastly more efficient. I don't have that level of complexity yet, BUT I understand what you are saying. (My next "mega thread" is on triggers and storied procedures, so consider yourself warned!) On 5/20/2020 at 5:06 AM, gizmola said: Again, you have stated you don't plan to use one, and that is your choice, but you shouldn't take it as a personal attack that in my experience building a model in a tool is a boon to productivity, quality and helpful in the iterative process of refactoring to address things that were initially overlooked. See my earlier comments. I think you got my threads and objectives mixed up. I want to learn Workbench - when I have time, which I do not have now - for all of the reasons you are saying. (We have "violent agreement" here!) But for naming constraints/indexes, i do NOT need Workbench. And now that I am pretty sure i can do all of that in phpMyAdmin, I will likely use a (lower-uality) tool instead of doing it by hand. (So I am still agreeing with you.) On 5/20/2020 at 5:06 AM, gizmola said: It's also invaluable for communicating with developers in a team. Perhaps you don't care, because you don't see a time when you won't be a one person team. If anything I've understated the value of an ERD, and that's why I use one for my projects, large or small. You're preaching to the choir. I think an ERD is the most important part of a system, with use-cases being second. (Data and processes define every system ever created by man.) And that is why learning Workbench would be a big gain for me - because it would be so much easier to update my ERD's, and also have an easier way - in theory - to go to FedEx and print out my entire system on a plotter. (I could tape all of my 8 1/2" x 11" mini ERD's together, but then that screws them up for putting them back in my binder. Thus my reason for wanting to do things electronically!) On 5/20/2020 at 5:06 AM, gizmola said: This was simply practical advice. I have worked on many a project where the originator neglected to make integer keys unsigned. Worse yet, they sometimes had foreign keys with a different definition from the primary key of the foreign table. I've also seen people create a small type table, as for example, one might create for the product_type values we've discussed, and make the primary key a bigint. Fwiw, in the 1990s I was trying to learn database modeling tools, but never got too far because there was no documentation nor all of the books and resources we have now. (That and the software cost thousands per seat.) So I have appreciated data modeling and tools for well over 25 years. On 5/20/2020 at 5:06 AM, gizmola said: Not too long ago I was working on a project for a service company that had many millions of transaction rows in one particular table, where there were about 14 "flags" or "status" columns. This was just one table, among many others, some of which had the same issue. The flag columns were typically booleans (0 false, 1 true). The status columns had a universe of 20 or less mutually exclusive values. With MySQL they could have used 1 byte tinyints, but the original developers made them all 4 byte integers. They were allocating 56 bytes on these columns per row, when they could have allocated 14. Compound this with a number of indexes on these columns, and the waste was in the terabytes. At that point there was nothing they could do about it, that didn't involve an expensive and painful maintenance process. That mistake had a very real cost to the company as the db was bloated, along with the indexes, which made all their expenditures on the rdbms infrastructure less effective. Good design is important, I agree! On 5/20/2020 at 5:06 AM, gizmola said: You were discussing some pattern that would give you referential integrity. As for OOP, a subtype is an OOP concept, as I've commented previously. In OOP you make a class, and you can have other classes that inherit from the a parent class. A class that inherits from a parent class is a subtype. I was referring to the Stack Overflow link I posted in my other thread. (Short version is that by adding a subtype to the UK, you ensure that a Supertype can only ever have one subtype per record. So I considered that a way to enforce logic WITHOUT "procedural code". But we debated all of that enough.) On 5/20/2020 at 5:06 AM, gizmola said: I would hope you plan to at least use component libraries. I'm not sure what that means. On 5/20/2020 at 5:06 AM, gizmola said: I have shared this video many times over the years (it's 8 years old now). PHP at the time was in bad shape, compared to its competitors. Then a group emerged from the symfony framework community that created the composer tool for managing component libraries and autoloading, and that completely changed the trajectory of the language. I highly recommend you watch this video, so you can understand the evolution to the current best practices for PHP development. Thanks for the video. When I have time I will check it out. Fwiw, IF I ever survive the pandemic,and IF I ever get my website done - of which I am skeptical - then once I get it filled up with content, probably my next steps are... - Lean MVC - Try to learn OOP (I mean *really* learn it. Not put out the garbage that 95% of OOP developers do.) - Try and learn Domain-Driven Design (DDD) and Miscroservices My dream is to finally master - at least the first two above - after struggling to find the time (and the right mentor) to help me get their. If I could get over those hurdles, then I could really start cranking out some cool systems, and in less than a decade like this project is?! Who knows, maybe you can help with some of that... Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578269 Share on other sites More sharing options...
gizmola Posted May 22, 2020 Share Posted May 22, 2020 54 minutes ago, SaranacLake said: Well, maybe after I really learn and master OPP I can look into ORM's, but that is over my head now. (Plus, are there even any open-source ORM's out there?) Every PHP framework I know of has some form of ORM, back to CakePHP and Codeigniter, to Zend Framework. The 2 big boy PHP frameworks are currently Laravel and Symfony. Laravel has Eloquent, and Symfony has Doctrine (which is a separate project). It's possible to use Doctrine with any framework, and it's not uncommon for people to use Zend Framework with Doctrine. There is another ORM named Propel that has a similar history to Doctrine and was once on equal footing within the Symfony framework, but Symfony has more or less adopted Doctrine as its standard now. These are all open source/free as in beer with open source licenses. If you have heard of Ruby on Rails, you might know that Rails is an MVC framework, but it comes with an ORM known as ActiveRecord. Several of the ORM's I mentioned are Active Record implementations. Active Record is an OOP design pattern. Doctrine, which I prefer, is a "Data Mapper" pattern implementation. It has some different concepts to it. If you don't use an ORM, you will likely try and build something yourself that has some of the properties of an ORM. You would do yourself a lot of good, on whatever phase you are on, to adopt one of these libraries, even if you aren't using every aspect of them. They provide the "model" portion of Model View Controller (MVC) systems. It is also possible to build a subsystem using an ORM even if the system has lots of existing code that doesn't use the ORM. I have done this on projects any number of times. 54 minutes ago, SaranacLake said: Thanks for the video. When I have time I will check it out. I don't think you really can afford to delay that. It explains autoloading, dependency management and component libraries. Even if you don't plan on using a full framework, what this explains is how the modern dependency injection frameworks are put together. They aren't a monolithic blob of interdependent code, but rather a number of component libraries that each do a different thing. The last half of the video is him going through a number of the symfony component libraries, with dead simple examples of things you can do with them in 5 lines of code. The world of professional PHP programming involves using component libraries now. If you aren't using them, you are doing it wrong. This isn't some thing you should get to some time in the future. It's been 8 years since this became the defacto standard way of developing PHP applications. Logging for your app? Component Library. Generate a pdf? Component Library. Send emails? Component Library. The video explains this and a bit on how to identify the ones you want to lean towards, as there are often many component libraries out there that solve a certain problem. Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578272 Share on other sites More sharing options...
SaranacLake Posted May 23, 2020 Author Share Posted May 23, 2020 21 hours ago, gizmola said: I don't think you really can afford to delay that. It explains autoloading, dependency management and component libraries. Even if you don't plan on using a full framework, what this explains is how the modern dependency injection frameworks are put together... i can afford to delay, because I don't know a lick about OOP or frameworks, and you'd have better luck teaching your dog Calculus than trying to teach me any of this late in the 4th quarter... I know how to program solid procedural code, and that's it. In an ideal world, I would have had the time and luck and a good mentor to hep me get on the OOP bandwagon in the late 1990s, and the same for the early 2000's when frameworks started coming out. But life isn't always convenient or fair. And, not to start another debate, but you can write solid systems using something other than OOP. So I have used the tools that I have, and the results are more than good enough. However I will be the first one to tell you that one of my goals in life is to become proficient with MVC and OOP and frameworks before I die. (Not sure that will ever happen, but it is a dream of mine.) Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578307 Share on other sites More sharing options...
gizmola Posted May 24, 2020 Share Posted May 24, 2020 On 5/22/2020 at 6:55 PM, SaranacLake said: However I will be the first one to tell you that one of my goals in life is to become proficient with MVC and OOP and frameworks before I die. (Not sure that will ever happen, but it is a dream of mine.) This is a self fulfilling prophecy of failure. First off, you don't need to be an OOP expert to use classes and objects. With all the time you've spent here explaining to everyone why you can't or won't take their advice, you could have already watched the video, and learned a few things about modern PHP. Consider this my white flag. I give up. 🏳️ 1 Quote Link to comment https://forums.phpfreaks.com/topic/310745-copies-of-data-in-subscription_details-table/#findComment-1578356 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.