I'm sure this question has been asked before, but I'd rather put a twist on it to keep it not opinion based, and as informative as possible. (Also please forgive any mistakes I make in annotation, I'm still fairly new to this and want to get better.)
Say I'm creating a database (SQL Server, a RDBMS I'm not familiar with) with a set of users, and for the sake of simplicity, I'm just keeping track of some APIs that these users use. Say I have API's Alpha, Beta, Gamma, and Delta. Each of these have keyed access. I need to associate the keys with the users. My gut instinct would be to split the data as such in schema A:
,-------, ,----------,
|*Users*| |*APIkeys* |
|-------| 1 |----------|
| id^ |----\ | key^ |
| fname | | | service^ |
| lname | <Has> | queries |
| email | | | ... |
| ... | \--<=| userid |
| ... | n | ... |
|_______| |__________| ( where ^ indicates primary key )
In this schema, the APIs table has a composite primary key, which makes sense from a high level perspective. I'm keeping track of APIs and Users. Each API has a unique key to its service, which seems reasonable to turn into a composite key.
There is alternative schema B, which is where instead of having one APIkeys table, I have 4 separate keys table, one for Alpha, Beta, Gamma, and Delta. This leads to a lot more tables, but avoids having the composite keys.
However, after thinking some more about the situation, I also have an alternate relational schema, Schema C:
,-------, ,----------, ,----------,
|*Users*| |*UtoArel* | |*APIkeys* |
|-------| 1 n |----------| 1 |----------|
| id^ |------<=| userID^ | n /---| keyID^ |
| fname | | keyID^ |=>-/ | service |
| lname | |__________| | queries |
| email | | queries |
| ... | | ... |
|_______| |__________| ( where ^ indicates primary key )
So now my questions are these (also, please keep in mind I'm using SQL Server, but I'd also like to know "in general" as well):
What are the advantages of schema A over B? A over C?
Are there any pitfalls of using composite keys? (It would be great if you can tie it in with this scenario too.)
Are there any other schemas that I missed that would be better?
And of course, any other advice would be welcome, I'm happy to learn.