SaranacLake

Yes. Based on my understanding of what the author published on Stack Overflow, I disagree. Let me refer to what was said by the author on SO... https://stackoverflow.com/questions/17405702/mysql-supertype-subtype-design So that is why I use ID + product_type_code. Without leveraging the "product_type_code", you can end up with this... Scenario #1: [code] PRODUCT ID=1 SUBSCRIPTION product_id=1 ONLINE_BOOK product_id=1 TSHIRT product_id=1 [/code] Whereas the author's approach gives you this... Scenario #2: [code] PRODUCT ID=1S ID=2B ID=3T SUBSCRIPTION product_id=1 product_id=1S ONLINE_BOOK product_id=1 product_id=2B TSHIRT product_id=1 product_id=3T [/code] (And THIS is what I was saying a while back about, "I won't need procedural code to enforce subtype relationships...")

Which was my earlier point of the merits of writing my own DDL's in SQL... For you, I don't disagree. For me, I do disagree. Some day once I have mastered Workbench, then I too can do as you say. You know, I really appreciate all of your help, and I don't doubt your years of knowledge, competency, and so on and so forth. But I also think sometimes you get a little shortsighted... 😉 On our design debate, while I can see most of your points, I'm not sure you can see things my way. And again, here, I think the same things is happening... One of the first rules of being a good teacher is not insisting that your pupils see the world as you do, and have the same experience that you would have doing something, but rather to help them reach THEIR potential. I know that "in theory" it sounds all so simple to install and use Workbench, or a PHP framework, or MVC, or OOP. And for you these things are likely easy. But I am not you, and you are not in my shoes... You have no clue the things I am facing right now in life, nor can you begin to understand all that is on my plate. Trust me. If keeping my head above water was my primary goal, I would throw PHP/MySQL and my website in the trash and focus on my important things. But I have come so far, and the finish line is in sight, and I am "playing the long game" and not just trying to survive this week, but have a better life down the road. And I am eager to master many of the things you talk about, but now is not the time. (If that still doesn't make sense, it's because you're looking at my problems through YOUR eyes and not through MY eyes...) 😉 If I thought Workbench was a better approach right now today, I'd be using it. But it isn't. If I am still alive (and not out on the street) come August, I will pick it up. Right. This I know. But it is an additional distraction and stressor that I don't have the bandwidth to handle, today. I appreciate the nudges and you playing "devil's advocate" - for anyone who truly cares about me, I *expect* that. But I also know ME better than anyone on the planet Earth. And I know my current situation, and you don't. AND, being an analyst (formerly) by day, I think everything I do out more than anyone you'll meet, and I stand behind every decision I make. So much of your advice is duly noted, and will be great for v2.0, but right now I am focused on eating each day, and shipping a competent v1.0 of my website... 😉

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.)

So true! But if/when you finally ship a working system that actually solves the problems that it is supposed to solve, it is one of the coolest feelings there is!! Hopefully I live long enough to see that happen with this project of mine?!

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?) And I'm trying my best to build a competent solution. So what would you call it? 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". Okay. And I appreciate that immensely!!! 👍 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. 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!!) 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!) 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.) 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!) 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. Good design is important, I agree! 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.) I'm not sure what that means. 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...

@kicken, Thanks for the insightful response! Okay, I follow you. Okay. So create the UK the way I think I'd be querying the most - in my case UK(order_id, product_id) and then create a 2nd index for the other FK. (But i won't need a 3rd IDX.) Yeah, I'm of that school of thought... Every table has an ID as the PK, with the exception being lookup tables. Okay. You can always redefine your indexes as things evolve, right? Okay. Yeah. I noticed that in the beginning, as well, and was wondering the reasoning behind it. Yeah, it probably would have been better to have one PK and be done with it, but I'm not expert in that area, so who knows?! Either way, I found that write VERY informative, and definitely helpful in what I wanted to do. (Even though Gizmola thinks I'm nuts?!) 😉

As my site grows it isn't practical to post everything, and it would be nice if some people - not you - would give me the benefit of the doubt before saying I'm stupid... Right, I know that. Well it is a join plain and simple. But, yes, the reason for the 2nd part of the composite key is to enforce that only ONE subtype can be chosen for a given record. Right, but the reason they did that is this.. In my case, the PRODUCT could have an ID=1 and then I could have a SUBSCRIPTION with an ID=1, a BOOK with an ID=1 and a TSHIRT with an ID=1. By having that additional key/join, a given Product record can ONLY map to ONE subtype - it's a pretty brilliant workaround when you can't use a Check constraint!! Right. Probably for efficiency. There are joins that ONLY is the PK, and the UK exists solely to enforce type. In my ERD, I know that I used just the PK in some place to do joins for efficiencies sake. But it probably would be better to lose the ID and create a composite PK as you mention. Design is subjective! Okay. Sound doubling up on Indexes is okay, right? Actually while I was waiting for your response, I had another question that relates to this last point... Let's say you have a textbook order system modeled out, and you have our favorite ORDER -||-----|<- ORDER_DETAILS ->|-----||- PRODUCT And the way I prefer to do things, I would have this for my junction table... ORDER_DETAILS - id (PK) - order_id (FK1)(UK1) - product_id (FK2)(UK1) - purchase_price - quantity Now, if I took what you were saying above, my UK1 would be correct one-way, but not the other way. So, in that case, I would have an index for FK1, an index for FK2, and then a composite index for UK1, right? And hopefully that would break anything in MySQL, nor would it slow things down having the extra index, right?

I said I might... Kind of like clicking around like a monkey in Workbench, huh? 😉 Seriously? Let's see... I know how to use phpMyAdmin. And I know how to write SQL. Yet I do NOT know how to install or use Workbench. So, yes, I said I would likely hold off using Workbench because I do NOT know it, and I do NOT have the time to learn how to use it properly. I am not "rebuffing" advice - you're not paying attention to what i said. If it was not possible to name or rename constraints and indexes in phpMyAdmin - and I actually didn't know you could - then it would still be easier to do it creating my own DDL's and running it in the SQL window versus learning a new GUI. But since it appears I can do it in a tool that I already know, that seems the way to go. Sorry, but I am not following what your concern is... If I use phpMyAdmin or learned Workbench, I wouldn't need to because the GUI does it for you.

True. Yes, I see that. Sadly, cPanel is being lame and only offers MySQL 5.7 so it will be at least until later this year before i could ever take advantage of them. So for version 1, those are out. You can actually name FK's and Indexes in phpMyAdmin - maybe it breaks things up into two steps as you mention? I don't know. (This would be another benefit of writing my own DDL's, is that I learn and see how things work. Although there is a good chance I';; just use phpMyAdmin for speed right now.)

Why take the lazy way, when you can be more detailed? https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/#explicit-naming I agree with nearly everything this guy says on his webpage...

Oh, so it is MySQL that is being a pain, and not phpMyAdmin. Hmmm... I was certain that you could rename the primary key to what you wanted, but i guess that is not the case?! Well then, I guess this is a moot point. Too bad, because I would prefer to use my naming convention. And, yes, I know you can let MySQL auto-generate everything, but then it becomes a real PITA to search for and manage things. (Only a lazy person would not organize index and constraints in his/her database!)

Any reason to be so nasty lately? It's a legitimate question, and a damn good idea to organize everything in your database.

Okay, glad I asked. Okay, there is the nugget of wisdom I was looking for! Why exactly do you have to do it in that order? Is that because if the UK starts off with the ID, then when MySQL starts scanning the composite key and only sees ID, it sorta ignores the rest? Aha, but that isn't so in my case... I only showed a snippet of the larger design, which includes me implementing a rally neat implementation of Super/SubTypes in MySQL. So, in the simplified example above, my PRODUCT table has an ID that is the PK. So if the PRODUCT table was in isolation, then yes, i agree that is all I need to uniquely identify records. However, I am creating a composite UK so that I can join my Supertype PRODUCT table with the SubType SUBSCRIPTION table, and those two tables are linked by using "id" and "product_type_code". Using this approach - and this is the cool part - ensures that there can only be ONE SubType that maps back up to the parent SuperType. See this link for the details... stackoverflow.com/questions/17405702/mysql-supertype-subtype-design

And I stopped reading when you chose to go down the "That's stupid" path...

Hello. So I have spent all night - plus another day last week - pondering the naming conventions that i want to use for my database. And I think I have a solid approach. But before I was ready to nod off, I wanted to ask this additional question... As I recall, when you create a table in phpMyAdmin, it assigns the name "PRIMARY" to your primary key. (I'm pretty sure it is phpMyAdmin doing this, and not MySQL.) So anyways, I don't really want to use that naming convention. Rather, I would like my primary keys to use this format... tablename_pk So, if I create the table using SQL and I name the primary key as I would like, will this cause any issues with MySQL or phpMyAdmin? I'm pretty sure the answer is, "No", but you never know?! Thanks!

Hello. I have a question about the correct way to do a FK and Index(es)... I have the following table... PRODUCT - id (pk)(uk) - product_type_code (fk)(uk) Here is what I know... 1.) In MySQL, you *must* have an index on a column in order to create a FK constraint. 2.) The "product_type_code" is a FK and points to a lookup table. 3.) The "id" + "product_type_code" need to form a UK because I will be using that as a join to another table. Questions: a.) Do I need to create a regular Index on "product_type_code" so that I can in turn assign a FK to that single column? b.) Or is it possible to double up and use my composite UK on the "id" + "product_type_code" both as a way to join those two fields to another table, PLUS as a way to serve as an index for my FK on "product_type_code"? If I had to guess, I would say that i should first create an IDX on "product_type_code", then create my FK on 'product_type_code' and then I could create the composite UK on the "id" + "product_type_code" columns... So which is the correct way??

I'll read up on this, and likely have a lot to learn and do over. Not sure when I'll get to this, though, I have this super-demanding shareholder over in the database design area that won't cut me any slack, so this mobile photo thing will have to take a back seat until I appease this other guy?! 😁

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!!) 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. 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 said once per Version, not once per year... 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... 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. 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! 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. You're very black-and-white on some things... 😉 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. Yes dad. I may not be perfect, but I am methodical, and I pride my work, so I will do the best I can. Good thing you aren't my boss, because I have convinced you of nothing!

Yes, one code, one use, plus in my usual paranoid way, i will likely tie the code to some PII so that Suzy Q cannot buy a "Platinum" plan and give herself 12 gift code, nor can she give all 12 to one friend. Probably tie the gift code to a name, email, etc to try and limit abuse...

Yes, Snagit has a "100% quality" option. I am using an older version of Snagit because I don't like how capture works in the newest version, but I will have to be sure whichever VERSION - there is that word again! - I am using takes advantage of my Retina's resolution. (Might have to upgrade.) I am guessing that it tricks the browser to think the image/capture is 1440 wide, so that on a regular screen, things look okay, and on a Retina screen, things will be 1440 wide but look acceptable since there is really 2880 pixels in the same space as a 1440. Something like that? On my Retina, Snagit has an option Advanced > Scale down retina imaging when sharing. I guess I would want to have that CHECKED so that things are 1440 wide for normal screens, but the 2880 kicks in on Retina screens? If so, I guess I screwed up, because that box is currently Unchecked, and all of my screenshots were taken as such. Of course, either way, I think I have to rethink how an online book might look and work on mobile which is something I never even considered when i wrote the book?! (Live and learn!!)

Yes, they do look like registration keys, and if anything, I think that makes things look more professional. But to be clear, there is no risk of reverse engineering, right? I mean I generate a code, store it in my database, and then when a gift receiver enters in the "gift code", IF it matches what is in my database, then i reduce the price to $0. I suppose a person could guess a code and get lucky, but it seems to me that is mathematically impossible consdering the "gift code' length, right?

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. 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? 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? 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. Where else would you store the contents for an Online Book other than in the ONLINE_BOOK table - and related ONLINE_BOOK_CONTENTS table?? Fair enough. But is it a mortal sin to choose the design I have chosen? In this case, no. I responded to this above. All it does is make my queries *slightly* more complex. Or am I missing something? 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... 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.!! 😠

How good is the quality of a screenshot taken with Snagit? Is there a way to take better quality screenshots? Isn't the quality of the screenshot purely a function of your monitors resolution? After all, if you are capturing your screen, then the resolution of the screen should impact the quality of the screenshot, right? All of my screenshots are taken on my Retina MBP, although I'm not sure how good Snagit it as far as high-resolution goes. (I do know it is superior when it comes to editing and marking up screenshots for business presentations and books like I am doing.) Thanks for the link! Okay.

So what is the proper way to do that? Do I save the raw screen-capture from SnagIt as a .png and then use something like Photoshop to make a smaller mobile size? Or after I capture something in SnagIt, do I use SnagIt to resize the image, and then save it as a smaller mobile size? I'm not sure if the sequencing impacts the image quality? Well, since my website will (hopefully) use proper HTML/CSS, yes, people will be able to easily save things and get a WYSIWYG. But for arguments sake, as far as other websites on the Internet, most of the time Save As HTML fails and Print to PDF rarely yields something workable? Trust me, I am someone who spends a good part of his day researching things and saving (thousands, if not tens of thousands of) web articles each year for later reference. Javascript breaks most modern websites. The days as File Save As are long gone on the Internet.

What do you mean by "registration keys"? The goal is to generate random, non-guessable codes that a member can share with friends or family. When the recipient enters the code into the form, I check that the code matches against available member gift codes, and if there is a match, I add a "redeemed_on" date, adjust the shopping_cart price, and then allow the recipient to check out. Now that is very cool!! Thanks!!!