Advice for MySQL database modeling

Question

I'm in need of some advice for creating a database model which holds data for multiple users. I will try to be as specific as possible. Please excuse me if this is too broad or inappropriate.

I'm creating a web application where users can sign up and add data about their sportsteams. After registration the user has to be able to add members to a table and add achievements to another table.

Right now i have 4 tables in all. members_table holds all members of a sportsteam. achievements_table holds all achievements. member_has_achievement holds the relation between a member and a achievement, and acts as the unique identifier.

And lastly i have a login_table.

This table has multiple columns: loginid username password email

My initial thought was to create a relation table between login_tableand the members_table. So that one user can have multiple members, and these members can have multiple achievements.

But I'm not sure this is the best way to do this. I build this model for a single user app, but I'm having trouble scaling it up.

Any advice for how I could model this differently?

Also you don't need member_has_achievement as long as you put the unique identifier in achievements_table (like an unique id for the achievement and the member has an unique id, so you can just insert this unique id in achievements_table). — Charlotte Dunois
– Charlotte Dunois, Commented Sep 19, 2014 at 11:27
@CharlotteDunois I think he needs member_has_achievement, looks like the achievements_table stores all available achievements, and the member_has_achievement is saying which ones the member has. So reasonable to have it (it's many-to-many relationship). — Daniel Gruszczyk
– Daniel Gruszczyk, Commented Sep 19, 2014 at 11:29
The user is the actual person that adds info to the database. He/she acts as the admin for a given sportsteam. The members are entities of the sportsteam. For example user adds member A to the database. And the adds achievement B to member A. — Karsten Tietje
– Karsten Tietje, Commented Sep 19, 2014 at 11:31

Daniel Gruszczyk · Accepted Answer · 2014-09-19 11:33:12Z

1

All seems good for now. Imagine you have multiple users, stored in your users table.
Now if a user can add a member, all you need is in a members_table, add your user_id as a foreign key:

-----------------
| Members_table |
-----------------
| ID : PK       |
| User_ID : FK  |
| Name          |
| anything_else |
-----------------

-----------------
| User          |
-----------------
| ID : PK       |
| email         |
| login         |
| password      |
-----------------

I don't see a reason why this wouldn't scale up. You can see by User_ID which member was added by which user etc... Basically this is one-to-many relationship : one user can have many members, but a member belongs to one user only, if I understand you correctly :)

User table here would be your login_table btw :)

answered Sep 19, 2014 at 11:33

Daniel Gruszczyk

5,64210 gold badges53 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karsten Tietje Over a year ago

Yes - I just was thinking if this was the best way. But as you pointed out it can actually work :)

Mark · Accepted Answer · 2014-09-19 11:45:52Z

1

I found this link useful for database designs for beginners.

Here, assumming that you are designing a database for students as users:

enter image description here

and for simpler model:

enter image description here

answered Sep 19, 2014 at 11:45

Mark

8,47115 gold badges52 silver badges81 bronze badges

Collectives™ on Stack Overflow

Advice for MySQL database modeling

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related