How to determine the type of data to use in this flexible case?

Asked

Viewed 74 times

1

The project consists of a database for RPG sheets (D&D and the like). It was like this:

Database
record sheet

Tables

  • character
  • system
  • raccoon
  • class
  • attribute
  • personagem_attribute

Relationships

  • CHARACTER = N ====== 1 = SYSTEM
  • CHARACTER = N ====== 1 = RACE
  • CHARACTER = N ====== 1 = CLASS
  • CHARACTER = N ====== N = ATTRIBUTE == PERSONAGEM_ATRIBUTO

In the "attribute" table there are attributes that can contain text (e.g.: Background), numbers (e.g.: Force points = 15), text and numbers (e.g.: Arc, Damage distance 2D6, Damage body to body 1D6).

How can I create this in the table personagem_atributo?

I thought of creating a column (attribute) with the name valor with the guy varchar, but that way I get stuck with just the guy varchar. How do I manage to assign type to value according to the information provided (int, text, varchar)?

mysql sql modeling varchar

  • Your question goes against the concept of RELATIONAL banks, but the only solution I know for your doubt is in the structure of Nosql Banks. In this type of bank each tuple has its individualized and changeable typing in the next.

  • Thank you so much for your help. I’m still new in the area so sorry if the questions are basic. But is the Nosql form still in use today? I saw that the relational model is the most current so I thought this would be the best model to be used in a database.

  • If the value can be alphanumeric, then there is no doubt, it must be VARCHAR. I think there is no need to complicate. If you need to later you can make a CAST for the kind you need!

  • Got it. Thanks a lot for your help.

  • @Hidemitsugoncalveshashimoto The answer solved your question? Do you think you can accept it? See [tour] if you don’t know how you do it. This would help a lot to indicate that the solution was useful for you. You can also vote on any question or answer you find useful on the entire site (when you have 15 points).

1 answer

2

There is no problem in doing this in a relational database. It may eventually not be the most suitable, but if you get out of the relational and almost everything else you need the relational then you get into bigger trouble still. It is better to have the ideal for the most part and the least ideal for the rest, but that is also correct.

You will work with key and value. There are several ways to do this. But you’ll have to treat it half by hand, but almost always not too different from what you’d have to do in a Nosql, After all he does not miracle, he frees you from the schema, but not having to deal with things you don’t even know exactly.

In some cases you can make it simpler, but it seems to me you want to use the EAV standard. Of course identifiers can be enumerations, or even strings, but it is much worse and costs much more, usually without need, as the Nosql does. I think even ideal that the id column is used and in an auxiliary table of available columns you have indicating which type it is to give more information than you need. This is much more efficient than the model used in Nosql.

In fact almost always the Noschema model of Nosql (wrong name of the technology) and the adopted EAV is wrong and it would be better to make a relational. There are few cases that lack the schema really is a good solution, even if it takes more work to do with schema.

Browser other questions tagged mysql sql modeling varchar

You are not signed in. Login or sign up in order to post.