1

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
9
  • You say you have 3 columns, but in your example give 4 columns. So the meanings of columns in your example are not clear. Please correct that.
    – zgguy
    Jul 11, 2015 at 10:08
  • 1
    IMO you are starting in the wrong end. Assuming (User_Id, Nickame) is a candidate key why bother creating a row_num column? Jul 11, 2015 at 10:09
  • @Lennart To tell the truth, I have tried to give some example absed on my situation. But, it seems that I didnt consider all things. I have updated my question. Jul 11, 2015 at 10:14
  • @zgguy Please read the question with more attention. I have said that, what if I add ROw_Num column to my table and then I give my example/ Jul 11, 2015 at 10:15
  • @Lennart I will be very happy, if i could explain the problem. If not, I can try to explain it differently. Jul 11, 2015 at 10:59

1 Answer 1

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.