I'm learning about RDBMS design and I was wondering if there are any really good websites that help with learning. In some specific cases i've found that a simple google search isn't enough; creating a database with a sub-child rather than just a parent-child gets a little bit tricky.

Using InnoDB, I was wondering if anyone had some awesome resources. I am currently wrapping my head around the ER diagram and how to apply it to most any situation; it's pretty easy for basic designs, but when you add more complexity it gets a little confusing.

This is something I think any website developer should know; not just for maintainability, but also for speed and modularization.

This post was edited by Glyph89 on Sep 28 2016 07:47pm

are you just looking for programs that help you diagram? you can look into visio, dia, and lucidchart. some tools will general the sql with the diagram and will generate the diagram based on the sql.

at work we have hundreds of tables, some with more than 100 columns. it's not always a fun place to be. wouldn't surprise me if we have over a thousand tables when everything is considered.

if you're starting your own project, you can also look into no-sql alternatives depending on how you use your data.

No, i'm currently using MySQL Workbench so that covers the graphical design.
I'm looking for more examples of databases that use for example A members table, a statistics table, and a class table. What I mean by this is when a user joins they pick a class (or classes) and then the win loss records are stored in the database. I'm trying to normalize everything but keep it linked together.

A good example here is this:

table_members
column_username
coilumn_password
column_tokenkey

table_classes
column_warrior
column_rogue
column_druid

table_statistics
column_total_wins
column_total_losses
column_ranked_wins
column_ranked_losses

What this needs to do is keep track of each members classes total and ranked wins and losses.

so i.e.
Frank has a warrior and rogue.
his warrior has 32 wins and 2 losses
his rogue has 2 wins and 42 losses

his total wins are 34 and his total losses are 44. ( i dont actually need to calculate the total wins and losses in the database since this is supplied elsewhere)

How do I link these together in seperate tables?

one way i thought of was using a member_id with a FK but i was told that isn't the best way of doing this so i'm trying to figure out the best way.

This post was edited by Glyph89 on Sep 28 2016 08:19pm

i'm not a big book reader i'm afraid, so i can't suggest much. this site has a lot of sample schemas if you're interested in looking at them: http://www.databaseanswers.org/data_models/



i dont know the full extent of your design, but your snippet looks strange to me. i'd probably do something more like this:

classes
id
code
description

members
id
username
password_hash
tokenkey

characters
id
member_id
class_id

statistics
id
character_id
total_wins
total_losses
ranked_wins
ranked_losses

instead of having a separate column for each class type, they're rows in your table. so you can easily add/remove classes.
for lack of a better word, `characters` is a specific instance of each class a member has. so frank would have two rows in the characters table, one for his warrior and one for his rogue.

This post was edited by carteblanche on Sep 28 2016 08:35pm

This is exactly what i was looking for lol. Just using rows instead of columns, i was under the impression that columns were faster at indexing and i couldn't get off the whole keeping them as a column.

initially i had planned on making the columns contain rows of member_ids, but your way makes alot more sense.

One thing i am curious about though is row locking, in innodb it seems table locking isnt as bad comapred to myisam; and i have no idea why.

edit: this however explains alot.
http://stackoverflow.com/a/28070969/5566036



This post was edited by Glyph89 on Sep 28 2016 09:12pm

i'm not a mysql expert. i know oracle has "select for update". you can look into documentation for it.

http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html



i think it was Knuth who said premature optimization is the root of all evil. IMO start with a simple, normalized design that's easy to maintain. if you run into performance problems, then redesign as needed (eg: denormalize, partitions, triggers, etc). if there's no reason to go out of your way to make everything more complex / less maintainable, then don't do it.

This post was edited by carteblanche on Sep 28 2016 09:34pm

hahaha true! I suppose i'll just keep a collection of unit tests as I go, but this is an entirely different discussion lol.

Thank you for your help!