Hi, I'm fairly new to database design. I am working on a personal project and I had a question about general design structure. I am trying to design a database that I want to create for a collectible card game. The idea is that each different type of card will have a table, so there will be a character table for the characters, an Item table for the Items, etc.. The character table would then contain all the pertinent information regarding the characters, IE stats, faction, card text, The item table would contain similar information regarding items, and so on. Originally I was thinking of having an overall table tying them all together, which would basically just have the Card Name (the Card Name being a unique Identifier in Card Games, I was going to use this as the primary key for this table, and a foreign key in the other tables) and the card type, which would indicate which table it is in (IE Stanley is the card name, and the card type is character, so it is in the character table).

I have a few questions regarding this design idea. First of all, would the overall table be useful to tie the tables together, or would them all sharing the key of card name be enough? Second, is there a way to implement a design that involves effectively indicating which table the card is in based on type? I'm not sure how to go about that, would I need to indicate this in some type of relationship?

Also, if anyone has any advice or suggestions, or any experience to share designing something like a collectible card database, any input would be appreciated. Thanks!

Why not put all cards in the same table, and use the type (of the card) as a foreign key to a type table. You can do the same with other card properties that are shared.

The only problem with this is that the Card type is not going to be a candidate key. It will not under any circumstances be unique. I might be looking at this too hard, the structure I'm looking at should probably work functionally, I'm mostly just thinking about how to make it a proper design. Thanks for the reply.

Edit: Never mind, I'm slow. I just realized what you meant by a type table. This may work, I'll look into this idea. The main reason I was thinking about doing the tables the way I have described is because the Character table will have different information than say the Action table. While the Character Table will have Attack, AC, HP, etc; The Action table will only have the costs, requirements, and text.

You could have columns that you don't use (NULL) for this type specific information (the easy way), or link the card by id and type, to a link table that contains this specific information (better design).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.