Clever Geek Handbook

Table ratios Many to many without an average table in sql?

I am creating an application that can search for creatures and I am in the process trying to increase my knowledge.

I have a Creature table and a Skills table

A creature and has several skills, and a skill can be used by several creatures.

I am coding in java using SQL manager.

I am using 1.2 to represent skills in the creature table and link to the skill table using numeric values.

I thought there was a way to make an overloaded stored procedure?

I haven't started coding yet while I plan, but would be grateful for any ideas sent to me.

I'm not trying to avoid the middle table, just see if there is a way to do it in a different way that isn't all that hard.

+3


source to share


2 answers


You will probably need a middle table.

Keeping a comma-separated list of skills in the Creature table makes it easy to get skills for each creature, but what if you ever want to know the creatures that have that skill?



Comma-separated lists are fraught with problems. You can use them to optimize one way to access data, but this leads to drastic de-optimization of other ways to access data.

See also my answer to Is a delimited list actually stored in a database column?

+8


source


If you are using a relational database, the "correct" and general way to solve it is the table that will store the relationship. If you want to avoid the average table, you can set a limit on the maximum number of skills for each creature - say max 5 skills and then have fields called skill1, skill2, ..., skill5. I cannot recommend this option because it will make the query more complex, but in some cases it is possible. Another improvement to this option would be a single int or long field, where each bit represents a skill. However, in my opinion, this is not very good.



+2


source






More articles:


All Articles