I am creating a web site to track lifting weights and calories. I have never designed my very own database before and that i figured I ought to get is really as just right as you possibly can before I start coding. Each time I believe I am done, I recieve antsy it's not perfect. I have mostly fleshed it in MySQL Work bench however i have a couple of questions.

  • How important could it be to create the database "perfect" before hands? How hard could it be to re-factor a database later?
  • Exactly what does the engine option do? Right now all tables are defaulting to InnoDB engine? Any difficulties with this?
  • Are dataypes changeable after data entry? Not going from VARCHAR to INT, but how about VARCHAR(45) to VARCHAR(255)?
  • What exactly are fundamental database rules that HAVE to be adopted for any effective design?

I have also attached my ERM diagram. If anybody could explain any apparent problems it might be greatly appreciated.

alt text

Start as perfect as possible allow it to be, make it as being imperfect since you need it.

No practical, operating database is ideal. Every one has their hpv warts.

Among the large benefits of utilizing a database is it could be created to withstand future change. For instance, use saved methods for data updates and adding data. If your table is eventually split, the saved process could be modified accordingly without any visible impact needed for just about any exterior software.

Should you hold back until the look is ideal, you may never circumvent to applying anything helpful.

Generally, attempt to get the organizations exercised, but adding fields, indexes, etc. afterwards isn't any large deal. Refactoring organizations to aid different cardinalities is sort of more involved.

Instantly, a few things come out... 1. All individuals designated fields inside your exercise table. HUGE warning sign. I'd refactor this immediately. 2. badges->userbadges. Even when you are enforcing a distinctive constraint on title, I'd still create a new PK for your table (an id). 3. I'd also take a while to normalize the naming of the id fields (i.e. in User, you utilize 'userid' however in food, you utilize 'id' rather than 'foodid'). Which way you decide to go is really a matter of preference but do play the role of consistent.

The issue having a "perfect SQL database" is the fact that SQL lacks the energy to create a "perfect database" beyond a particular trivial level.

But, pay attention to your instructors making Codd proud (3NF/BCNF and all sorts of that). It is a good bit simpler to include caching or temporal not-stabilized data later (for performance, as it's needed), etc, than to fix a database filled with bad data. Data wants love. Like it.

That being stated here are a few factor I'll explain:

  1. exercise looks wrong, wrong, wrong -- secondary1, secondary2, secondaryN ... no thanks! I wouldn't leave the schema such as this!

  2. userid is really a PK (varchar) but usernumber is definitely an int? Again, no thanks. This does not seem to stick to the same format because the other tables (the majority are id) and really should be considered a tip-off that something might not be right. Possibly the userid (or username?) must possess a unique constraint (you can include indexes with other posts too -- PKs generally "perform bestInch with monotonically growing values, although DBs must have a re-index schedule too).

  3. Sporadic naming (userid versus usernumber versus id). No thanks. I do not care that which you choose -- but please it consistently.

Just my quick review. Happy developing.

It appears very good ... except your exercise table. The secondary1 to secondary10 fields smack of something which should most likely maintain another table. However, you have done well otherwise, so maybe this will make sense? Consider it and make certain.

Perfectness does not matter, things can (and can) change after you are done. Attempt to normalize lower so far as you are able to (you have done this mostly already). Area dimensions could be transformed, don't be concerned about this.