SaranacLake

When someone buys a "platinum" members, they get several trial gift subscriptions to give to friends and family. What would be the best way to generate unique, random codes that are maybe 8-12 digits long? The workflow I came up with last night does this... In MySQL, I have defined which gift subscriptions are linked to a given Product Versions. (Hint: @gizmola) 😉 Then when someone subscribes, I go to my GIFT_CERTIFICATE_DETAILS table, determine the number of free gift subscriptions, and then my PHP will need to generate that many codes and insert them into my GIFT_SUBSCRIPTION table. - id - subscription_id - product_id - gift_code I am thinking that it is better to give people a numeric code? And maybe something 8-12 digits, so it is long enough to be unique across all time, but not so long or complicated that it's a pain in the ass to type into the web form to renew them. Thoughts?

@gizmola, I got something right for once?! 😄 Yes, I am familiar with "responsive web design". Unfortunately, I have already bitten off too much and haven't had time to learn it and apply it. (To give you some perspective of my predicament, I was trying to learn and implement RWD 5 years ago when it would have given me a leg up, but a lot of life has happened, plus when you try to be a "Jack of all trades" you end up with an unfinished website 5 years later like I have!! Assuming I ever get my stupid website online, the first thing I am going to focus on for v2.0 is Responsive Web Design!! Right. I use SnagIt it a lot for my screenshot. I believe all it offers are .jpg, .bmp, and .png So what would you advice i do there? I would also like to point out that images are *complicated* for the reasons I mentioned above - if you have followed the entire thread. Recap: If I was hypothetically writing an online book called "Teach Yourself Adobe Photoshop in 21 Days", then what would I do with all of the screenshots I took using SnagIt where I captured my entire screen on my Retina MacBook Pro? If you have any experience with Adobe products, you certainly can appreciate how many concepts require that you see most, if not all, of the screen. So to @kicken's point, yes, for some things (e.g. menus, palettes, etc) I could capture something that would easily fit in the screensize of a smartphone. But what do i do when you need to see the entire application window? I cannot image a person could look on their iPhone or Android at an image that is what, 2.5" x 5" in size? So what is your advice to deal with that? Remember, if you are reading an entire book on learning a software application, and I have maybe 20-30 screenshots PER chapter, and a book could have 10-20 chapters, then that would seem like an enormously daunting task for someone to read an entire book on a smartphone, right?! I chose online books primarily because of fears of piracy, yes. But as a geek, I am also used to reading everything online, in a browser, on my laptop so it seems like the most natural way to read and learn. (Web pages scale automatically, where PDFs don't.) Yes, I realize there are lots of tools and add-ons to capture web pages, but in my opinion it is MUCH easier to buy a book in PDF format and give it to the rest of the world for free, versus trying to save 500-1000 pages of an online book. Btw, being an expert on capture web pages, I can tell you that Save As HTML and Print to PDF rarely work on modern webpages - although on my website they would probably work WYSIWYG.

@gizmola, Genuinely trying to understand your suggestions... 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. 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.) 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... 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.) 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?!) 😉 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?! 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. 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. 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. 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.

@kicken, Thoughts on these follow-up questions from last night?

Okay.

This topic - as discussed in an earlier thread - is confusing from a language standpoint because a "UK" in MySQL is both a "constraint' and an "index". From a constraint standpoint, if one column is unique, then combing it with two other columns would still guarantee unique values. But my followup question as asking about performance, as in "indexes". If you have three columns, column 1, column 2, and column 3. And column 3 has a UK constraint/index on it, if you then used those three columns as a key to a child table, it is NOT obvious to me that MySQL would perform as well having just a constraint/index on column 3 versus having another constraint/index on column 1 + column 2 + column 3...

Good point! But from a database performance standpoint, wouldn't there be a benefit of having the outer UK? For example, if I use those 3 columns as a quasi-PK to join to another table, wouldn't MySQL be able to find things quicker if I had a second UK on those 3 columns?

I have a junction table which has a column that needs to be unique. However, that field, along with two other fields - each which is the foreign key to another table - when combined together form yet another unique value. So in MySQL, can I put a UK on the nested field, and then combine that with the other two fields and create an outer UK as well? Thanks.

The plan was to take my MS Word book, convert it to HTML (and CSS), make sure it displays okay in a test template, and then paste the HTML into the BODY field in a given record in the BOOK_COMPONENT table. Sound right? And based on what you have been saying, if I do my HTML right, and make things "responsive", then I will only need that one record/field to handle things for desktop or mobile, right? Oh, and for v1.0, the plan was to store all images as files in an image folder on the web server. Sound okay? Thanks!

Well, it wasn't that simple, because I had it so when you hovered over a thumbnail the larger image loaded. Trust me, in that particular situation I needed Javascript. But that was a completely different beast, so it doens't matter either way. Very good point! The only problem is that at least one of my books deals with configuring your OS, so in that case a person might be more inclined to use a smart phone, since most normal people don't own two computers, right? How big would an image have to be on your average sized smartphone to have enough quality to show details of, say, a screenshot of Photoshop? 100k? More? Fwiw, the problem I ran into with my photo gallery is that the larger images were like 2-4MB because they were of landscapes and I felt that you needed that level of quality to appreciate things. With screenshot of a computer application, I can probably get away with smaller file sizes, right? Good idea. (Or even just put in a disclaimer that if they choose to use the mobile version, the quality may not be so great, but if they use the desktop version, then they can expect better quality screenshots, right?) That's good. I just happen to work with lots of 20-somethings, and if it weren't for their work laptops, I wonder if they would even know what a computer is!! That's good to know!! Fwiw, while my entire website is sadly built for desktop only, v2.0 will be responsive, and I'm not as worried about people going purely mobile on my main website. I just think people will have a poor experience trying to read an 800 page, 20 chapter book on something like Photoshop from their iPhone/Android! True. Yes, many of my books will be dealing with computer applications, although like I said, some deal at an OS level, so I'm not sure what people will do there. me, I have 5 laptops to chose from, but I'm guessing most people will only have one desktop/laptop, so if they are dealing with tweaking the OS, then they might turn to their smartphone since some of the things I cover require installing things, reboots, etc, and so you'd need a completely separate device to learn. But more of my books will be delaing with a dedicated application like Photoshop. Yeah, that is what I am hoping to do. Also, as is implied in your comments, I really need to get some metrics to know if I have any customers, who those people are, and what they expect. So I'd prefer not going too far down the mobile rabbit-hole until I know for sure I need to. At the same time, I guess what I was driving at in my OP was trying to make sure that I architect things NOW so that regardless of which direction I go later on, my backend database and frontend Ui scale smoothly. And on that note, do you agree that have a BOOK table and then a one-to-many BOOK_COMPONENT table were each record would hold a book chapter. is that okay? or because of mobile, would I need something more granular? (You or someone else said that was okay, but just double-checking now that you understand better what I'm trying to do.) Thanks.

In the past, I would have agreed with that statement. But everyone at work tells me that almost NO ONE users laptops or desktops anymore - unless they are at work. So my fear is that - especially with people under 40 - that EVERYONE will buy my book expecting to be able to read it on a smartphone, and then they will have a bad experience, and then I will get a made customer screaming for a refund?! In fact, I know lots of people at work - all younger - that do not even own a computer?! (They do EVERYTHING on their smartphones...) So how worried do I need to be about all of that? Yeah, I may have to do that. Sadly, I naively wrote my book last year from my laptop, and I just assumed that was how people would see things. Maybe that is another questions... Based on your personal and professional experience, how much do I need to cater to "Let me read an entire computer tutorial book on my smartphone" group of people? If it is 50-60%, then I need to be worried and will have to redo everything I have. If it is 25%, then I'd probably just move forward with what I have, and then after I go live, try and go back and offer a more mobile-friendly version later on. Thoughts?

Not poor implementation - just not knowing Javascript. And to be clear, my thumbnail gallery also loaded the full-sized images as well, so when you clicked on the thumbnail the full-sized image loaded. (Without using Javascript, there is no way to point to a thumbnail and a larger image and not have your browser load both the way that I coded things. Well, with how I did things, that wasn't an option. But either way, what I did in the past is not what I am trying to do here. If I have an online book, there won't be thumbnails, but just the regular sized images. (Since I haven't converted my book from MS Word to HTML, I don't know what size the images will be, but assuming you are reading the book on a laptop, I am thinking most images would be like 600 x 800? Maybe you have a suggestion?) When done correctly, I agree! 🙂 But I learned the hard way with my other - largely unrelated - website, that what I *assumed* would be easy was not! And I read on the Internet enough - mainly the news - to know that even large websites often struggle to load images in a reasonable amount of time, and I end up twiddling my thumbs waiting for the image to load before I keep reading. I appreciate the "over thinking" things, BUT I have also been doing this long enough to know that 90% of IT system problems come about because people were too lazy or dumb to ask the tough questions up front. (I have made a career of fixing enterprise systems because nobody did any planning up front!) Based on the little I know, it seems that the AMP project would take care of these concerns, but that leads to another concern - I don't have another 6-9 months to get really good with Javascript?! (It may be easy for you since you know it, but I'm just trying to get this dumb website online, and I'm afraid that implementing something like AMP might take me longer than I have for v1.0.) Okay, but back to what I asked @maxxd... If you were trying to learn new software, and software that is not easy to learn, e.g. Photoshop, then would you do it on a smartphone? Furthermore, could anyone in their right mind expect to be able to have legible screenshot of the entire screen of something like Adobe Photoshop fit on a smartphone screen? And even if the screenshot could fit, you'd still have to scroll like the dickens to read the corresponding text! I would pray that people wouldn't go smaller than an ipad, but i don't know, so what do you think? (I need to get a better sense of what your average user would do, because i could build an awesome solution made for desktops and laptops, but if everyone reads my book on their iPhone, then I'm gonna have problems on multiple levels. (And since I am not a smartphone user, I can't just go off of what "I" would do!) You guys are younger and probably more "hip" than me, so you can probably answer these questions better than I can. 🙂 Well, I have been doing documentation and training as part of my day job for decades. And, yes, in may instances, you can simply do that. But I use Photoshop as an example of where often you'd want to see the entire screen, or at least most of it. On an iPad, I could adapt screenshots and text to make it so you can see one logical step in one screen. But on an iPhone? That is pushing it unless the book was about MS Word or something easier. On the website I built last year - and someone was helping me out, so I don't recall all of the specifics - I know that the issue I ran into was that the thumbnails and larger images all loaded at one time, and I was told - based on that particular UI design - that the only way to avoid that was to use Javascript which I didn't have time to learn. I believe I'd have the same issue with my book, because - again - you do not have thumbnails in the chapter of a book, you load all of the regulr sized images at once - unless you are using AMP or "lazy loader" which I think are the same things. So even if using the < picture > element would allow me to adapt from desktop to mobile, it would NOT address the issue of 30 images loading when someone goes to to Chapter 5. Right?

I understand that, but I'm asking from a practical standpoint... Let's say you bought an online book on "Intro to Photoshop" and as you might expect, it contains as many screenshots and images as it does text, because you can't teach people how to do Photoshop in just text. And as you might suspect, there would be lots of screenshots where you'd need to see the *entire* screen to make sense of what is going on, so presenting a crop of the upper right hand corner of the screen wouldn't cut it because you might be using a tool palette on the left side of the screen and another palette of read out on the right side of thes screen. If you were on an iPhone or Android, how pleasant (or painful) would it be trying to following along? (Sure, you could grab and pinch to zoom in and out, and then move left and right to find what you need to look at, but even if that was doable, how could you read the text/instructions while doing that? And like most books, this book could be several hundred pages long with many chapters 0 and where a "page" means 8" X 10" and not what you'd see on a smartphone. Personally, I would *hope* nobody would try and read one of my books - with tons of supporting screenshots - on their smartphone, but if lots of people would do that, then I would need to know and try to come up with some sort of compromise.

Maybe if you buy an Amazon or B&N book, but if I created my own EPUB or MOBI, I don't think it comes with any DRM but I could be wrong. What about my questions/concerns about trying to read a tutorial with lots of photos/images/screenshots on a smartphone? I'm not sure how I could make that work?

Well, an iPad I can see because the screen is like a small laptop or notebook, but on a smartphone? Does your tutorial have pictures/images/screenshots? If so, how in the world can you see them on a smartphone? I mean I can understand how you can see a picture of someone painting a fence, but let's say you were reading a book on how to use a pivot table in MS Excel or how to use the alias tool in Photoshop - you'd have to have eagle eyes to see those screenshots! Since the books I have written so far of how-to guides on using software and working with your os, and thus have lots of detailed screenshots, I'd be surprised if anyone could read them on a smartphone, but since I myself don't use mobile, I just don't know. (For me personally, I would much rather read a web page or PDF or book on at least a laptop if not a desktop.) I am pretty sure the media queries just determine which image gets displayed, but they don't have the ability to stop your web page from downloading a larger image, but maybe I'm wrong. No, all hand-coded. Yes, I have thought about this and am sorta torn... Originally my fear of PDFs and the like was piracy. After all, once one person buys the PDF book, then they have the master copy forever, and they can turn around and email that to all of their friends and even put it on a file downloading site. My thinking of offering an online book version is that it would be much harder for someone to steal the book - although if someone had the gumption it would be doable, of course. Also, as a geek, I thought on online book would be better since it adapts to device sizes and is just easier to work with - in my opinion - than a PDF where you might have to zoom in or out, and keep adjusting things as you change pages. (With a web page, it automatically fits and all you need to do is scroll.) I also thought about offering eBooks, but I have the same fear of piracy as with PDFs.

@maxxd Yes, I have done that for another site I built. What do you think about my questions above? Would any sane person buy a tutorial book (e.g. on using some computer application like Adobe Photoshop or MS Excel) and expect to do so on a smartphone? If so, then how should I handle the images? I cannot image someone could go from reading text talking about something, and then look at an image that is sized to the a small smartphone size and be able to even see what the image is about without a lot of zooming and scrolling. Then again, it perplexes me that most younger people are more comfortable surfing the Internet and even reading eBooks on a smart phone versus on a normal sized monitor like a desktop or a laptop, I believe that is what the AMP project does, right? And to my fear, that requires knowledge in Javascript.

@requinix, Well, the reason I was concerned is that last year I made a small website with several photo galleries. Each gallery loaded with thumbnails, and then you could click on a thumbnail and load a larger picture. Because I don't know Javascript, I had to load everything at once - although I did optimize the photos the best I could. At any rate, the larger galleries (e.g. 50 - 100 photos) took MINUTES to load, and so I am fearful that a 40 page chapter with 20-30 screenshots could have the same issue. I believe that a lot of the larger media sites (e.g. NY Times) uses AMP to help with image loading, and I could use that. But the problem is that I am trying to just get my website up and running, and so I have to weigh having a "usable" site versus a "perfect" site. (Of course if it took minutes to load a book chapter, that might get me in hot water!) Can I still have the concept of a BOOK -||-----|<- BOOK_CHAPTERS and do that? No, I just mention pages because my books are written in MS Word and so I was trying to give you a sense of how big things are. To me, what matters are the parts of the book (e.g. cover, table of contents, chapters, index, etc.) The books I have written are tutorials, and rely heavily on images to help people "see" what to do. (If you removed the images, my books would fall flat.) That being said, what do I do for mobile? (I don't use a smart phone, so I'm rather ignorant on how these younger generations interact with the Internet and websites and all?!) Apparently the majority of people now access the Internet entirely on mobile devices, right? If so, then would they also use mobile to read a "hot-to" book? Specifically, would any sane person read a book that is a tutorial and heavy on images on a mobile device?? For example, "Introduction to Photoshop". (I would think that would be extremely painful to read and learn that way, but who knows what Millennials and Generation Z thinks and does?!) I ask, because the answer to those questions will impact how I design both my back end and my front end. How difficult is AMP to learn and implement for someone who doesn't know Javascript?

This question is part PHP and part MySQL... For my site, I need a design that will allow me to store and display and online book. Let's say that I have a chapter that is 40 pages long, and contains 10-12 images, where a "page" is a page in MS Word. Currently, I have an ONLINE_BOOK table and then a one-to-many relationship to an ONLINE_BOOK_COMPONENT table. For version 1.0, images will simply be stored on a file-share, although I am interested in seeing if I could store the images in MySQL for a later version. Anyway, on to my question/concern... If a person loaded a webpage (simple HTML/CSS) that was 40 pages long, including 10-12 images, that could cause some loading issues - at least with the images. And if a person was on a mobile device and tried to do the same thing, I suspect would cause real issues?! From a database standpoint, I'm not sure of how I could logically break things down smaller than into chapters - especially since chapter size in a book will vary. (One chapter might be 10 pages, and another 50 pages.) The only way I can think to make sure that the web page - which will represent a chapter - loads quickly is to use something like the AMP project. But are there any considerations I need to consider from a PHP and MySQL standpoint? Thanks!

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. I just addressed this. 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? 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.

Okay, I didn't know that. 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. I have: PRODUCT ===> SUBTYPE-A ===> SUBTYPE-A_VERSION PRODUCT ===> SUBTYPE-B ===> SUBTYPE-B_VERSION PRODUCT ===> SUBTYPE-C ===> SUBTYPE-C_VERSION Total symmetry. But see my earlier comments... Why do in 4 tables what you can do in 3 tables? Verbose even for me, but I can see the benefit. See earlier comment. 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.

@gizmola, 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? 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. 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. 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?! I have that in my design plus a lot more to help "incentivize" renewals. Which includes pricing. 😉 What part don't you like as far as naming goes? Was there a place I was not normalizing based on the little I told you? Again, I know I didn't give you much, but what do you feel I got wrong? I have that. 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.) Columns that apply to *all* subtypes would go into the supertype, products. Agree? 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. 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. No, it's much much more. But legitimate questions! 🙂

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?

@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. You and someone in Beijing are related if you go back to Adam and Eve, but there is no immediate connection, right? 😃 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 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. Simple answer is because the way I set the subscription price and renewal price depends on WHAT you did and WHEN you did it. 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. 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.

Based on what I have on paper, I think it is a good idea to allow people to buy subscriptions while they are shopping in my online store. However, I will only allow people to buy one subscription per order, just like if you straight up buy a subscription from the menu bar. If someone tries to add a second subscription to their cart, then I'll pop up a warning and explain only one subscription per order. Shouldn't be too hard to do when I code things. Can't say how users will react to this, but there's only one way to find out?! Just figured I'd bounce it off of you all... 😉

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?