MySQL Forums
Forum List  »  MySQL Workbench

Data Modeling Question
Posted by: Steven Gifford
Date: July 14, 2009 11:16AM

Please be patient in reading this as I am not great at explaining things. I have a project that requires a database and I am more than a bit rusty, and not all that experienced with projects of this size. I have reached a point in my data modeling where I see two paths before me and I am unsure which, if either, is the best way to go. Any input would be appreciated. Here is the scenario:

I need to manage an unlimited amount of groups of people with ten people in each group. Each person in each group will have exactly three coins in his / her pocket. Each group will exist for no longer than 30 days and then be disbanded. I do not need to track history other than the people themselves. One person can be in any number of groups, but the coins in their pocket may be different in one group than in another. There is the potential for over 1000 active groups at any given time.

On the surface this seemed a fairly simple design. I have one table for people, which stores first name, last name, and issues a unique person id. I have another table that stores active groups. It stores group names and assigns each group a group id. I have a third table of coins that stores the name, value and coin id for each potential coin. This is where I ran into my dilemna...

My first thought was that when a new group is created I would simply add a row to the Active Groups table and create a separate table for that group, say GroupA, which would then hold the person id for each person in the group as well as the id of each of their coins. This way, when the group disbands I can simply drop the table and delete the row for Group A from the active groups list. This path seems to make the most sense to me, but it creates a situation where I would potentially have a database with over 1000 tables.

My alternative path is to once again add a row in the Active Groups table when a group is created, but rather than created a group-specific table I would store all of the group members in one table with a separate entry for each instance of their membership. So I would have a table named, say Members, and within each row of the table I would store person id, group id, and coin id. This way, when a group disbands I would only need to delete the Group row from Active Groups and delete rows that reference that particular group id from my Members table. This seemed to work as well but I quickly encountered a snag. Aside from the obvious issue that it leaves me with one table that has, potentially, 10,000 rows, there will be several rows that all reference the same person, just in different groups. I cannot get my mind around whether this is proper. Is each row an instance of a person, or an instance of a group member?

Lastly, from a coding standpoint by far the easiest way to store that information would be to create not a Members table but a Coin_Instance table. This would store a reference to one coin per row in person id, group id, coin id format. This would be useful in that there will be times in group interaction when one person gives away a coin to another, or they swap, etc. The issues here are two-fold. First, it seems that I am now up to potentially 30,000 rows in this one table, and second, I have now tripled the number of duplicate person ids. Of course, if the table is a record of coin instances then the duplicate person ids are fine. They just don't seem terribly efficient.


Options: ReplyQuote

Written By
Data Modeling Question
July 14, 2009 11:16AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.