Where am I most likely to get into trouble?

[SaranacLake]

So I am reviewing all of the pages and pages and pages of ERDs that I have, and hope to start building things in MySQL tomorrow.

Those of you who know me, understand that I'm terrified of making some gigantic mistake on my website, and so I often tend to fret longer than necessary?!

In abstract terms, where would you say people most commonly get into trouble when building databases from the ground up?

Or put another way...

What kind of database design mistakes tend to be "fatal"?

My hope is that as long as I've included all of the necessary tables, and as long as I have things normalized (or denormalized where necessary), that everything else should be relatively easy to fix - for example changing data-types, adding/removing a few columns, changing indexes, etc.

Thoughts on this?

 

[kicken]

I generally find the best thing to do is just create your tables and start working.  You'll probably make a mistake/forget something in your initial table design but that's fine.   You catch it while you're writing the code and then update your database as needed.

There's a point at which trying to plan ahead become counter-productive.  You spend far too much time trying to anticipate everything you'll need/want and either make things overly complicate or still miss something anyway.   So instead just put in the basic stuff you know for sure you'll need and then let the design evolve as you work. 

I generally don't even bother with indexes outside of the primary key / foreign keys until I have something mostly developed, tested and working.  That way I have a better understanding of what my actual queries are rather than trying to guess what they will be early on.

Pretty much every mistake can be fixed, some may just take longer or be more complicated than others.  Most of your mistakes will probably be minor like "Oh, I should save this too" or "This should go in a different table" or "Maybe I should have an index here". You'd be hard pressed i think to make any mistake that I'd classify as "fatal". 

 

Bottom line, don't worry about mistakes and trying to get things right the first time.  Spend more time actually working the problem so you know what you need rather than trying to think of everything first.  Don't be afraid of re-factoring things later on, possibly multiple times as your understanding of things improves.

[SaranacLake]

@kicken

17 minutes ago, kicken said:

I generally find the best thing to do is just create your tables and start working.  You'll probably make a mistake/forget something in your initial table design but that's fine.   You catch it while you're writing the code and then update your database as needed.

There's a point at which trying to plan ahead become counter-productive.  You spend far too much time trying to anticipate everything you'll need/want and either make things overly complicate or still miss something anyway.   So instead just put in the basic stuff you know for sure you'll need and then let the design evolve as you work. 

All good advice, but I still worry...

 

Quote

I generally don't even bother with indexes outside of the primary key / foreign keys until I have something mostly developed, tested and working.  That way I have a better understanding of what my actual queries are rather than trying to guess what they will be early on.

Good point.

Yeah, that is sorta what I was hinting at in my OP - that hopefully having to change indexes or even keys after you go-live isn't the end of the world.  In my mind, what is the most important is that you capture the right data and have the right relationships.

For example, if you don't store "Order History", then later on you can't re-create it out of thin air?!  And if you didn't have an "Order Details" table, but instead hard-coded 10 slots in your "Order" table, assuming no one bought 11 items, you would be safe, BUT you'd have a real mess to clean up later on!!

 

 

Quote

Pretty much every mistake can be fixed, some may just take longer or be more complicated than others.  Most of your mistakes will probably be minor like "Oh, I should save this too" or "This should go in a different table" or "Maybe I should have an index here". You'd be hard pressed i think to make any mistake that I'd classify as "fatal". 

Let's hope you are right about the "fatal" part!!!

 

 

Quote

Bottom line, don't worry about mistakes and trying to get things right the first time.  Spend more time actually working the problem so you know what you need rather than trying to think of everything first.  Don't be afraid of re-factoring things later on, possibly multiple times as your understanding of things improves.

Well, hopefully i am at that point in my design where all of this is true.  (Although I did find one problem area last night that I need to quickly re-work, but other than that, I think my database design is solid...)

 

Thanks!

 

 

Edited July 30, 2020 by SaranacLake