Hello, I was hoping sombody could provide some input into a design issue I am having.

I am building a system that will collate data from multiple poker networks. Here are the rules and how I think I should build it.

I have many poker networks. Each poker network can have many members, which have differently formatted IDs depending on the network. I need to ensure that any given poker network only has a single member with a unique ID.

Example:
NetworkID, networkMemberID
1, 123456
2, abcde
1, 123456 (<-- this is not unique, so it shouldn't be here)

Normally I would solve this with a paired primary key, on networkID & networkMemberID.

I then need to store data on each unique player, on multiple date. The (slightly illogical) approach to this for me, would be (NetworkID, networkMemberID, date) as the primary key, which im pretty sure is the wrong approach.

So perhaps I'm thinking I should instead have the members tables designed with the fields UIN, NetworkID, networkMemberID, and use the UIN field in the other table (combined with date to ensure uniqueness). However, I still need to ensure that the (NetworkID / NetworkMemberID) pair are unique. Which makes me believe I need another table, but I become a bit lost here.


What also becomes confusing later is that I need to associate many of these 'unique' members, to a local 'unique' user. So, user 99999 is associated with 5 different members (only 1 per 'network').

That is probably stupidly confusing, so I will draw up an ERD of what I have so far.

Thanks for any help guys. I think I just need a little push in the right direction of thought, and I'll be OK from there.

EDIT: Note, I'm using MySQL

This thread may be of some use to you.

the use of composite keys for example...

and yes an ER diagram would be a great help