Struggling to design what seems a simple DB design problem
I am trying to design a MySQL database for a sport clubs (cricket), but if you don't understand that sport it doesn't matter!
The basis of my problem is that the club has a number of members, including playing members, non-playing members and also ex-members all of which I would like to have in the members table.
At the other end of the scale I want to records statistics for matches. In the ER diagram below I have the table which records the stats for each batsman in a match.
The personal data which is specific to that batsman, such as their name, is recorded in the players table. The only reason that I have the names inserted in the players table and not just use the data from the members table is that the players table also displays the names of opposition batsman who aren't members of the club! There is an obvious problem here in that I'm replicating data, such as names, which is not ideal.
The other possible model is shown below. Instead of a player table there is a table for the club's players and a table for opposition players. Although this eradicates data replication I'm not sure if it is a good design. Mainly because I would have to be asking the database questions based on the 2 foreign key fields in the batsmen table (instead of the one with previous design) and that when one of these columns has a value, the other one has to be empty or is NULL which isn't very good design either, or at least I don't think it is!?
I hope from what I have described people can try and give me advice and point me in the right direction or if someone has any alternative siuggestions that would be great. And if I haven't explained myself well then please say so!
I've been trying to work this out for a bit so any suggestions would be appreciated.
To deal with your last point first, yes that was a mistake I made in the diagram, I would've aimed to make it a multiple column primary key using the match_id and player_id. Thanks for pointing it out though.
I can see the advantages of having all the members/players in the one table. I'm just a bit concerned about the number of empty/null fields that this would create. I would only have the telephone numbers, email addresses, postal addresses, player profiles etc of members of the club. With this approach if we go a few years into the future the massive majority of rows will be non-members meaning that a large part of the table will be empty, i.e. no personal details.
Should I just live with this though? or is there another way? or am I worrying myself too much!!?
I take the point on the NULL fields, makes me feel a bit better!
and i dunno about "massive majority" either -- i would expect there to be more members than non-members, based on your earlier description
I think we might have some crossed wires, hopefully not too crossed though.
Based on what you said I thought it made more sense to put all players (club players and opposing players) in the one table along with non players. Seeing as they are all people it seems strange to separate them into separate tables.
I can then add a column which determines the person's status i.e. if they are a playing member/a non playing member/or not a member at all.
So I will have all players and non playing members of the club in this table, along with all players that play against us. At least that was what I was thinking after your comments. Sounds ok? And so , for example, the club could have a total membership of about 200 which would increase steadily over the years; but the total of all the opposing players could add up to maybe 700 people in a year. Over the years I would think that the opposing players in the table would increase quicker than the club members in the table. Which leads to my comment that there will be a lot of NULL entries. Do you agree on that one?
i think if i had all the playing members/non playing members/or non members in the one table as I said (I'll call it the PEOPLE table), and then have a member_id foreign key which links to a members table - the members table does not include members' first name and surname (so that this data is not replicated) but the members table does include the personal details which are just appropriate for members....email addresses, telephone numbers etc. This will then mean that I won't have all those NULLs in the PEOPLE table.
The only issue is that when a person is not a club member in the PEOPLE table, what should be displayed in the FK members_id in the PEOPLE table? A NULL value?
Actually should the PEOPLE table have the FK in it....which table is the parent table? If the PEOPLE table is the parent then maybe the FK should be person_id in the MEMBERS table?? oooh this is fun!