Performance of primary/foreign key versus single table with no primary key

Question

If I have a user that can be associated with multiple keys would the proper table setup be:

1. One table with two columns such as:

   UserName | Key
   

   where there is no primary key and a user can have multiple rows, or:

2. Two tables with an matching identifier

    Table 1     UserName | UserId
   
    Table 2     Key | UserId
   

   where UserId is the primary key of table1 and the foreign key of table 2.

Which way is more preferred if I wanted to find all the keys associated with a user?

Answer 1

Without an understanding of the entities and attributes you are attempting to model, it's not really possible to give you an answer to the question you asked.

Entity Relationship Modeling

What entities does your data model represent? An entity is a person, place, thing, concept or event that can be uniquely identified, is important to the system, and we can store information about.

From the description given in the question, we are thinking that a "user" is an entity. And maybe "key" is also an entity. We can't really tell from the description whether that's an entity, or whether it's a repeating (multi-valued) attribute.

What uniquely identifies a "user"?

What attributes do we need/want to store about a "user"?

The second part is understanding the relationships between the entities.

To do that, we need to ask and get answers to some questions, such as:

How many "users" can be associated with a specific "key"?

Does a "key" have to be related to a user, or can a key be related to zero users?

Can a "key" be uniquely identified, apart from a user?

And so on.

Based on those answers, we can start to put together a model, and evaluate how well that model represents the problem, and how well that model is going to work for our expected use cases.

If both "user" and "key" are entities, and there is a many-to-many relationship between the entities, the model for that is going to look different than if "key" is not an entity, but just a multi-valued attribute.

If a key must "belong" to one and only one user, and a user can "hold" zero, one or more keys, likely it's a multivalued attribute. Then we need two tables. One "parent" table for the "user" entity, and another "child" table to store the repeating attribute.

We don't know (yet) what set of attributes uniquely identifies a user, so we'll represent that with a generic "userid" attribute of some unspecified datatype.

user
-----
userid  datatype    NOT NULL PRIMARY KEY
name    varchar(30) NOT NULL

e.g.

userid  name 
------  ------
psimon  paul simon
agarfu  art garfunkel

To store a multi-valued attribute, we use the PRIMARY KEY of the entity table as a foreign key in our second "child" table.

user_key
--------
userid   datatype NOT NULL FOREIGN KEY ref user.userid 
key      VARCHAR(30) NOT NULL

e.g.

user_key
userid  key
------- -------
psimon  G major
psimon  A major
psimon  A major
psimon  B minor
agarfu  A major

If we decide that "user" will have a different column as the primary key, then we'd use that same column as the foreign key in the child table.

In this example, we've allowed "duplicate" values for "key" for a given user. If we only want distinct values of "key" for a user, we'd need to add a UNIQUE constraint on the (userid, key) tuple.

---

Before we get too worried about performance, we need to concerned with getting some workable data models. From there, we can translate that into some implementations, and evaluate performance characteristics of each of those.

If the implementation has tables that don't have a suitable primary key, we can introduce another column to stand in as a "surrogate" primary key.

Answer 2

There is nothing common or uncommon in this case! it all depends on your business requirements; If a user can have multiple usernames, you need to have a table to link all these usernames for each user together, identified by a userId, and this userId should be the identifier of the user throughout your database design, therefore, you need two tables in this case:

UserDetails that will contain the user info, such as name age...birth date...etc, and UserNames that will contain the at least one userName for each user.

Otherwise, you can use the same table UserDetails to store userName along with the rest of the user details

So, in your case, use a separate table to store userNames, why in an example:

Supposing you have a user with two userNames for him.

if you use one table for storing user info with the userName, you will have your data like this:

Name       BirthDate      OtherDetails   UserName AnotherDetails
user       1/1/1990        blah blah..    user1    blah blah...
user       1/1/1990        blah blah..    user2    blah blah...

As you can see, your data in the above table is repeated

But if you used two tables, data size will be reduced

This is called database normalization

Answer 3

If you wanted to find all the keys associated with a given user you might use the following JOIN query:

SELECT Key
FROM keys k INNER JOIN users u
    ON k.UserId = u.UserId
WHERE u.UserName = 'username'

The place which would benefit most from an index in this case would be the UserId columns in the two tables. If this index existed, then, for a given user, looking up keys in the Key table would require roughly constant time.

Without any indices, then MySQL will have to do a full table scan for each user, as it tries to find keys corresponding to that user.

Answer 4

So long as your table has a unique PK you're basically correct and somewhere on the spectrum of "perfect" to "could do better".

In your first case, you're still correct, just that the PK is both UserName and Key.

The second one is more common and probably more correct because sooner or later you'll want things against users that bear no relation to the key and logically fit on the UserName table.