SaranacLake
-
Posts
648 -
Joined
-
Last visited
Posts posted by SaranacLake
-
-
21 hours ago, gizmola said:
My point with phpMyAdmin is that it is a tool that hides all the details from you. So yes you can go in and make a table, add indexes, relationships etc., table by table. Than you can go in and add things. The expectation of the tool is that its for people who don't really know SQL.
Which was my earlier point of the merits of writing my own DDL's in SQL...
21 hours ago, gizmola said:It's ubiquity is largely a function of the fact that it's been around a really long time. The basic ingredients are:
- 1st time creating a table = CREATE TABLE
- Changing something = ALTER TABLE
But neither of these is a script you can play and replay. The time it will take you to make all the tables with phpMyAdmin is guaranteed to be equal to or greater than the time to make an ERD in workbench, with none of the quality, and no ERD diagrams to look at when you're done.
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.
21 hours ago, gizmola said:I refer you to the other thread on foreign keys and the subtype. Having read your post:
- I Installed Workbench on my mac
- ran it for the 1st time ever
- Made a model of the tables (click type click drag)
- Related the tables (click drag)
- Generated the diagram
- generated the SQL I put in the thread
Took me about 15 minutes or so, and I was doing this while talking to my kid.
I have never used Workbench to make a diagram ever before. There was one non-intuitive thing I had to figure out but overall it was a lot more functional than I expected.
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...) 😉
21 hours ago, gizmola said:There are things I've mentioned like domains, that I probably would want to use that I didn't attempt to figure out in it, but again I spent absolutely no time learning it.
Now I grant you that I've used about 5 different commercial tools over the years, so I know what I'm looking for, but I just don't believe it would take you that long to figure it out. I'm also sure there are probably 50 video tutorials on the basic of it on Youtube.
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.
21 hours ago, gizmola said:Also FYI these tools including workbench have a reverse engineering feature where they will read either a live db schema or a db dump and bring the existing model into the tool.
Right. This I know.
21 hours ago, gizmola said:Again, I'm beating a dead horse here. I think you've built these things up in your mind. Using workbench to digitize your diagrams is not the same as becoming a master of photoshop.
But it is an additional distraction and stressor that I don't have the bandwidth to handle, today.
21 hours ago, gizmola said:I will do my best not to bring it up again
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... 😉
-
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.)
-
On 5/20/2020 at 3:18 AM, gizmola said:
Haha,
Well nobody said modern system development was easy.
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?!
-
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...
-
Thanks for the insightful response!
10 minutes ago, kicken said:The order you want depends mostly on how you'll be using the index in your queries.
I'd venture that in an order system you're most likely going to be joining orders to the details then to the products to generate invoices and such, which would have queries like:
select * from orders o inner join order_details od on od.order_id=o.id inner join products p on p.id=od.product_idA query such as that would want your UK defined as (order_id,product_id). With that order the UK can be used to enforce your foreign key relationship on the order_id column and be used to speed up the join between the orders and order_details tables.
Okay, I follow you.
10 minutes ago, kicken said:If on the other hand you were doing a query based on products, such as to find popular products you might do queries like:
select * from products p inner join order_details od on od.product_id=p.idFor that kind of query you might consider the (product_id,order_id) order so you could use the UK to handle your product_id foreign key and the join to the products table.
Okay.
10 minutes ago, kicken said:In either case, assuming both order_id and product_id are foreign keys then your UK can satisfy the index requirement for one of them and the other would need it's own index. Neither order is particularly bad or good in this situation as you need at least two indexes anyway.
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.)
10 minutes ago, kicken said:In such a table design you don't necessarily even need your ID column so you could just make your PRIMARY KEY be (order_id, product_id). Some people prefer always having a auto_increment primary key but in the scenario proposed it's not necessary and could be removed.
Yeah, I'm of that school of thought... Every table has an ID as the PK, with the exception being lookup tables.
10 minutes ago, kicken said:More indexes mean more index management and disk space usage. That can potentially lead to slower performance when inserting/updating/deleting data, but wouldn't have any significant impact on selects.
Okay.
You can always redefine your indexes as things evolve, right?
10 minutes ago, kicken said:As such, you should try and limit your indexes to only those that you absolutely need to make your system function well.
Okay.
10 minutes ago, kicken said:Until your system grows to a very large scale (millions of rows), it's unlikely you'd notice any problems from extra indexes though.
Yeah.
I noticed that in the beginning, as well, and was wondering the reasoning behind it.
10 minutes ago, kicken said:There's no efficiency to be gained from having the separate PK and UK there as far as I am aware. A single multi-column primary key would be just as effective and save an index.
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?!) 😉
-
1 hour ago, kicken said:
It is in the case you setup in your original post. I know you had some other thread ended up talking about this whole object-oriented sub/super type thing but I didn't really pay attention to it so am unfamiliar with the details. Looking at just this thread in it's own context the answer that your unique constraint is unnecessary is factual.
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...
1 hour ago, kicken said:In your original post you show that ID is a primary key (signaled by the (pk) bit). By definition, a primary key is unique across the table which means it's the only thing needed to find any particular row in the table when doing a join or a search. As a result, including it in another unique index is mostly pointless as discussed before.
Right, I know that.
1 hour ago, kicken said:Your link describes a somewhat different situation from what you originally posted. The unique constraint is there because it's necessary for a foreign key in another table, and that foreign key has more to do with enforcing row types than to support joining of tables (as implied in your question "..using that as a join to another table").
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.
1 hour ago, kicken said:In all the tables in that stack exchange example, the veh_id column is the only thing strictly necessary for the DB to handle all the joins and link the tables together.
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!!
1 hour ago, kicken said:The rest of the columns have to do with your type enforcement.
Right.
1 hour ago, kicken said:I'm not sure why they used separate primary/unique keys, I think the whole thing could have been done by making a single multi-column primary key which might make the whole thing easier to understand.
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!
1 hour ago, kicken said:Because indexes work essentially left-to-right, so if you want to take advantage of a column being part of an index, it either has to be the left-most column of the index, or you also have to specify all the columns prior to it. If you define the order as (id, product_type_code) then you can't use that index to search for a product_type_code unless you also specify an ID (and since ID is unique, that makes the product_type_code mostly useless).
However if you define the order as (product_type_code, id) then you can search for a product_type_code using that index and get all the matching ID's as a result.
Okay.
1 hour ago, kicken said:Applying this to your stack exchange example, it also means that you in fact cannot double-dip on your indexes and will need to create extra ones to support your foreign key definitions. Because veh_id is the value that ties all the tables together, you want it to be the first value in your unique / primary key constraint. As a result the veh_type_code will need it's own index to support that foreign key definition.
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?
-
13 minutes ago, gizmola said:
I thought you were planning to write the DDL.
I said I might...
13 minutes ago, gizmola said:Clicking away at phpMyAdmin like a monkey is about the least efficient way to do this I can think of.
Kind of like clicking around like a monkey in Workbench, huh? 😉
13 minutes ago, gizmola said:You were arguing to me that you didn't have time to learn how to use mysql workbench and make an ERD! This is the type of response that you sometimes use to rebuff advice, which leaves many of us with the impression that you rationalize your predispositions.
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...
13 minutes ago, gizmola said:You absolutely should have a full sql script with all the DDL to create all the tables, and constraints.
If I use phpMyAdmin or learned Workbench, I wouldn't need to because the GUI does it for you.
-
12 minutes ago, gizmola said:
Those are reasonable standards, but you have to keep in mind that postgresql and mysql have significant differences.
With Mysql/InnoDB there is no difference between keys/indexes and constraints. Of course constraints have optional on update and on delete configuration, but in terms of the underlying mechanics of indexing, an fk constraint creates a corresponding index.
True.
12 minutes ago, gizmola said:I don't want to get into re-writing the MySQL manual, but there are now check constraints in the latest versions of MariaDB and MySQL I really haven't used, but I only mention this for completeness.
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.
12 minutes ago, gizmola said:The only way you can name an index or constraint is to create it separately from your create table statement via alter table.
So you'll need to decide if naming things via a convention that is different from the defaults is important enough for you to have to break up the activity of table creation into multiple create and alter statements, and of course keep track of this fact should you need to make changes during the development phase and in the future, which in my experience is inevitable.
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 minutes ago, gizmola said:
There are some things you need to name yourself, so having a standard way of doing it, isn't a terrible idea. The counterargument to your concerns is that you don't need to organize anything, because the data dictionary already maintains the relationships between these database objects. If you want to find something, you will be able to by querying the data dictionary.
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...
Indexes should be explicitly named and include both the table name and the column name(s) indexed. Including the column names make it much easier to read through SQL explain plans. If an index is named foobar_ix1 then you would need to look up what columns that index covers to understand if it is being used correctly.
-
2 hours ago, kicken said:
The MySQL manual says:
So it would seem you are stuck in that case. I generally use SQL Server these days which does allow the name to be specified and I use PK_<tablename>. I use a similar format for other constraints, just with different prefixes/suffixes. How you name them doesn't really matter though, many people just use auto-generated names and it works just fine.
2 hours ago, kicken said:The MySQL manual says:
So it would seem you are stuck in that case. I generally use SQL Server these days which does allow the name to be specified and I use PK_<tablename>. I use a similar format for other constraints, just with different prefixes/suffixes. How you name them doesn't really matter though, many people just use auto-generated names and it works just fine.
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!)
-
9 hours ago, requinix said:
Guess again.
Too bad.
Doesn't matter. Nobody cares what the names of your indexes are as long as you didn't manually name them something stupid.
Any reason to be so nasty lately?
It's a legitimate question, and a damn good idea to organize everything in your database.
-
1 hour ago, kicken said:
The foreign key constraint needs a suitable index on the column, but it doesn't need to be an index dedicated to that column.
Okay, glad I asked.
1 hour ago, kicken said:So yes, you can double up your unique index if you create it properly.
Okay, there is the nugget of wisdom I was looking for!
1 hour ago, kicken said:In order to re-use it, the foreign key column must be the first column in the index, so when you create it you want to do CONSTRAINT UQ_blah UNIQUE (product_type_code, id) and not CONSTRAINT UQ_blah UNIQUE (id, product_type_code).
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?
1 hour ago, kicken said:As mentioned though, in the scenario you laid out, the unique constraint is entirely unnecessary as your ID column is already unique by virtue of being the primary key so you'd just make a simple index on the product_type_code column.
In a scenario where Id wasn't a primary key and a unique constraint was necessary then you could double-dip like that.
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
-
9 hours ago, requinix said:
No. Stop that.
The ID is a primary key. It is unique by itself. Do not combine it with some other column to create a composite key. That's stupid and wasteful and did I mention stupid.
Technically no, because if you don't then MySQL will do it for you. Which is what the documentation says, so either you didn't read the documentation or... well, that's it.
I stopped reading your question when you mentioned the dumb composite key thing again, but the answer is probably either "no" or "don't do that because it's stupid".
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??
-
20 hours ago, gizmola said:
Yeah the OS believes that the desktop is 1440 wide, but there's actually 4x the pixels. There are some good blog posts that explain how this stuff works if you are curious.
Right, well, it's still going to be 1440, so depending on the size of the screenshot when embedded in the document, that might be ok. Ideally you would have the be 720w or less, or whatever relative size you have. If it's too late for that at this point, then it's a point of reference for your process the next time around, or if you do a revision.
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?! 😁
-
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!!)
QuoteStep 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.
QuoteI 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.)
QuoteYou were just arguing that your subscription product will rarely change price (perhaps once a year).
I said once per Version, not once per year...
QuoteIn 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...
QuoteWhen 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.
QuoteMySQL 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!
QuoteHydration 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.
QuoteAgain I can only provide advice, even if you choose to ignore it.
You're very black-and-white on some things... 😉
QuoteIf 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.
QuoteHopefully 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.
QuoteGood luck with the project
Good thing you aren't my boss, because I have convinced you of nothing!
-
17 minutes ago, gizmola said:
Very unlikely, given the number of possible permutations. Furthermore, I would assume your system would track redemptions and limit them to a single use.
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...
-
19 hours ago, gizmola said:
I don't use snagit but I do use a similar tool which has preferences you can set for the raster image format and quality. I'm going to assume that snagit has similar settings. I set mine to create a jpg with 100% quality, so that no compression is utilized.
Yes, Snagit has a "100% quality" option.
19 hours ago, gizmola said:The resolution of the image in pixels will be whatever your screen resolution is although snaggit might have a retina specific option, as my tool does.
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.)
19 hours ago, gizmola said:In the case of the tool I use (monosnap) that option is "shrink retina snaps." More about this in a minute...
A full screen snapshot on my 15" macbook is 2880 × 1800.
But what does the browser actually believe the resolution to be? It actually thinks that the resolution width at full screen is 1440. This is the trickery that Retina plays. You can google more about it if you want the details.
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?
19 hours ago, gizmola said:So if I choose the "shrink retina snaps" monosnap reduces the screen shot to 1440 pixels wide. At this point, assuming you don't plan to remake all your screenshots, you have whatever you have. Hopefully your original images are high quality, and you retained those.
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!!)
-
18 hours ago, gizmola said:
These look a lot like registration keys for commercial software. The problem with registration schemes for many commercial products is that the scheme gets reverse engineered and then people are able to generate their own "legitimate" keys that register and unlock software. I was just pointing out that these might look like a registration key, but they aren't.
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?
-
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.!! 😠
-
6 hours ago, gizmola said:
Yes, make your screen shots however you want them at highest quality. With most quality mobile phones these days, as well as computers with high density displays like the macbook retina displays, you want your images to be 2x the desired size. So if you are going to display an image at 300px wide, then you want the image to be 600px.
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.)
6 hours ago, gizmola said:Here is an article that provides a visual demonstration of the noticeable difference in quality. On an Iphone or Samsung Galaxy, this could make the difference between a screenshot that is blurry and illegible and one that is clear.
Thanks for the link!
6 hours ago, gizmola said:Having done some mass conversions in the past, Photoshop has tools for automating conversions, using Automate|Batch
Okay.
-
8 hours ago, gizmola said:
The point is that like thumbnails, you have to create different sizes and qualities of the image for mobile.
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?
8 hours ago, gizmola said:Actually I would disagree. When things don't print well, that is often because styles have not been applied appropriately for all uses or specifically for print purposes. It might not look great, or even correct, but the print to pdf option is going to work, and if that doesn't then saving as html is going to work. Someone who really wants to distribute your content is going to have no problem doing it.
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.
-
8 hours ago, gizmola said:
As these are not registration keys AND they still have to be validated, you can simply generate a string of random numbers and characters, perhaps with some dashes between them.
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.
QuoteHere is a simple routine that will spit out strings in the format of xxxx-xxxx-xxxx, with 256^6 combinations (281 trillion+) combinations.
function generateRandomCode() { return substr(chunk_split(bin2hex(random_bytes(6)), 4, '-'), 0, -1); }
Very low chance you will generate duplicate codes, but you still want your DB to have a unique index on gift_code, and recover should you try and insert a duplicate.
Anything like this would work, as again these are not registration codes, are unique, and would be linked to a particular user.
Now that is very cool!!
Thanks!!!
Question about FK and Indexes
in MySQL Help
Posted · Edited by 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=1product_id=1S
ONLINE_BOOK
product_id=1product_id=2B
TSHIRT
product_id=1product_id=3T
[/code]
(And THIS is what I was saying a while back about, "I won't need procedural code to enforce subtype relationships...")