Sql-server – Is it ok to create additional column for helping other column to be a PK

database-designnormalizationoracleprimary-keysql server

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details:

Here is the table columns:

User_Id, Nickame, Age

Of course, for now there is anot any column or columns which are candidate to be a PK(composite or not).

In order to create PK, I have added Row_Num column to the table, which will start from 1 for each user. I will calculate the Row_Num in insert trigger based on the User_Id.

For example, table content will look like this:

15, 1, Assassin, 18
15, 2, Fighter, 14
15, 3, Gentleman, 5

16, 1, Lady, 5
16, 2, Clever, 15

Of course, the Pk will be UserId and Row_Num.

Could you please tell me is that idea Ok or not? If not, why and what is the better choices?

Is it ok? Or, I must include ID column wit autoincrement option?

UPDATE:

Let's say user will enter his nicknames in order. And one user can have same nicknames in different ages. And a user can have 3 nicknames in same age. And the firsta nd the last of nickname can be same:

15, 1, Assassin, 18
15, 2, Fighter, 18
15, 3, Assassin, 18

Best Answer

If I understood you correctly, you have two sorts of entities to manage:

  • Players (human beings that participate in your game)
  • Characters (fictional entities that the players use/manipulate in your game)

Once you look at your problem like that, you could model it easily like this:

  • players table, with a player_id as PK and information about your real users
  • characters table, with a character_id as PK and information about those game entities
  • A relation table to map characters to their owners.

(From a practical point of view, the relation table might be extraneous - you could store the owner as a column in the characters table .)

This allows you to use identity columns or sequences for both player and character IDs, and you don't have to worry about tricky triggers for inserts.

Related Question