Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

3 Neutral

About SaranacLake

  • Rank
    Advanced Member

Profile Information

  • Gender
  • Location
    New York
  • Age

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. And it prevents you from losing precious thoughts! What I have learned - the hard way - is that a [ quote ] tag near the end will often slurp up uncommented text at the very end - and code too - and once that happens you cannot edit things, which is stupid. When I have a quote near the end, with text following it, I try now to cut the quote after it is in, add a few carriage returns before the ending text, then paste the quote back it, and usually that works, but not always, plus sometimes I forget and then get burned. This is a pretty big bug in the forum software if you ask me.
  2. P.S. The way this website converts [ quote ], and [ code ], tags to an object is a real FUBAR... I had to completely re-type y last post because this is this site trashed my post. nd as you can see, the [ code ] tags are still not working?! *rolls eyes*
  3. 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...")
  4. 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... 😉
  5. 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.)
  6. 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?!
  7. 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...
  8. @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?!) 😉
  9. 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?
  10. 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.
  11. 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.)
  12. 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...
  13. 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!)
  14. Any reason to be so nasty lately? It's a legitimate question, and a damn good idea to organize everything in your database.
  15. 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
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.