Database Structuring of Data

Question

I'm thinking of the most efficient way to store data , priority is retrieving of data fast.

I have a set of data. Let take it as for example a Pokemon Card

Imagine that in this world , each card has it own unique ID but there can be duplicate properties of each card.

I'm thinking of creating 2 table to store such data

1st Table

Properties of each card that exist in this world with a unique ID

2nd table

for each ID , it can contain multiple rows of Unique ID of all the card in the world.

In summary , i'm thinking of having 1 table to store properties , another table to store who own this card with that property.

is there any better method to store and retrieve data. Planning to use mysql. Let's just take it as there can only be at most 100 million unique cards

Halcyon · Accepted Answer · 2013-10-16 16:17:08Z

3

So you have cards. Cards have properties. Some cards have the same properties and I assume properties are complex entities, they have some properties too.

It would make the most sense to have 3 tables: one for cards and who owns them. One for all the unique properties and then a many-to-many table between cards and properties.

Your select queries have a double JOIN. If you add indexes to the primary keys of all the tables it should be super fast.

Cards
  id
  owner
  name

Properties
  id
  value
  foo
  bar

Cards2Properties
  card_id [fk]
  property_id [fk]

answered Oct 16, 2013 at 16:17

Halcyon

57.8k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexandre Nucera Over a year ago

Good thing about this suggestion is that you can also add new properties without changing the model.

Collectives™ on Stack Overflow

Database Structuring of Data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related