0
\$\begingroup\$

I'm working on a database to back up my online TCG. It's currently SQLite, but I'm going to transition it to a MySQL database later.

Currently, I have three distinct types of cards with their own unique fields. For example. However, all cards have a single uniqueid that I use to refer to them.

TABLE cards {
    CARD_ID as INT
    CARD_TYPE as INT
    TYPE_ID as INT
}

TABLE creature_cards {
    CARD_ID as INT
    TYPE_ID as INT
    NAME as TEXT
    FLAVOR as TEXT
    ATTACK as INT
    DEFENSE as INT
    ELEMENT as INT
    FLAGS as INT
    MANA_COST as TEXT
}

TABLE spell_cards {
    CARD_ID as INT
    TYPE_ID as INT
    NAME as TEXT
    FLAVOR as TEXT
    SPELL_TYPE as INT
    ELEMENT as INT
    MANA_COST as TEXT
}

TABLE mana_cards {
    CARD_ID as INT
    TYPE_ID as INT
    NAME as TEXT
    FLAVOR as TEXT
    ELEMENT as INT
}

I'm trying to figure out how to build a query that will allow me to get all fields from a specific table without knowing the table I need to pull from ahead of time.

Basically, I want to be able to supply CARD_ID, query the cards table, and use the CARD_TYPE and TYPE_ID to return the row from creature_cards where CARD_TYPE is 1, spell_cards where CARD_TYPE is 2, and mana_cards where CARD_TYPE is 3.

I'm currently doing this through two separate queries and doing the logic on the backend, but I'm wondering if there's a way to get the data all in one query, because my queries can be quite time consuming.

Would it just be better to jam all card fields into one table, rather than keeping them in three separate tables?

\$\endgroup\$
1
\$\begingroup\$

Would it just be better to jam all card fields into one table, rather than keeping them in three separate tables?

That depends on 2 things: How many you have and how different they are. Basically if you don't aim to have more than several thousand items (that's just a random number order I throw, it's system and query dependent), you are probably better of with just 1 table, it will save you a lot of time.

That's brings me to the second point. It depends on how different the cards are. If I look at your schema, you have 5 common fields between cards, and the rest is a bit different. That's in my opinion not enough to justify having different tables. The logic is the same behind each cards (they are used in the same way, only with different effect?).

However, your main concern over this seems to be performance. For performance, it depends on the frequency mainly of your calls. If you have in a database all of your possible cards, then you don't need to query the database more than once. You just load all of them in memory and go through the memory whenever you need to, it's way faster than accessing disk.

Finally, to answer your question, you can use JOINS (examples). And if you are interested, see the performance of joins here.

Hope this answers.

\$\endgroup\$
1
  • \$\begingroup\$ Yeah, I looked at JOINS a fair bit while I was researching for this question. I also looked into quite a lot of stuff regarding variables (via temporary tables) and subqueries and even CASE statements for working out something viable. I'm thinking that you are correct in that the best solution is probably just jamming it all into one table and horking it all into memory during server initialization. I'll do some experimentation, but mark this guy best after a day or so just in case another answer presents itself out of the wild in the intervening time. \$\endgroup\$ – Terrey West Aug 15 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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