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.

the way i would do it, there would be only three tables: one for the players (all types, including members, players, and opposition batsmen), one for the matches, and one for the match_players

note that in each of your two diagrams you have match_id as the PK of batsmen, but of course this means that there can be only one row per match_id (probably not what you had in mind)

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!!?

not empty, null -- there's a huge difference

console yourself with the idea that nulls don't take up any appreciable space

and i dunno about "massive majority" either -- i would expect there to be more members than non-members, based on your earlier description

I take the point on the NULL fields, makes me feel a bit better!
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?

yes, that makes sense

i might still use the same table, maybe not -- i guess that would fall under the "art" of database design

try to look ahead to the type of query you'd have to use in either scenario

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!

yes, the people table should have the FK to the members table

that way your matches would have FKs to the same table (people) for both players

the FK from people to members would be nullable

ok Rudy that's about it, thanks very much for helping and pointing me in the right direction. Hopefully I now have the best design for the future!

It's quite a challenge designing a database based on predicting what will happen in the future!