How are you aware when to produce a new table for much the same object types?

Example: To understand mysql I am creating a model photo voltaic system. For that reasons of my project, planets have numerous similar characteristics to dwarf planets, centaurs, and comets. Dwarf planets are almost completely just like planets. Centaurs and comets are just not the same as planets as their orbital path has more variation. Should I've got a separate table for every kind of object, or whenever they share tables?

The example is most likely too simple, but I am also thinking about guidelines. Like must i use separate tables just just in case I wish to make planets and dwarf planets different later on, or are their any efficiency causes of keeping them within the same table.

Normal forms is what you need to have an interest with. They virtually would be the convention for building tables.

Any design that does not break the first, second or third normal form is okay by me. This is a pretty lengthy listing of requirement though, so It is best to go see clearly from the Wikipedia links above.

It is dependent on which kind of information you need to store concerning the objects. When the information its them is identical, say orbit radius, mass and title, you'll be able to make use of the same table. However, if you will find different qualities for every (say atmosphere composition for planets, etc.) you'll be able to either use separate tables for every (not so stabilized) or have one table for fundamental qualities like orbit, mass and title an additional table just for the qualities which are unique to planets (along with a similar table for comets, etc. as needed). All objects could be within the first table only planets could be within the second table and linked via a foreign answer to the very first table.

It's known as Database Normalization

You will find many normal forms. By using normalization you'll undergo metadata (tables) and focus the relationsships between data more clearly. Using the normalization techniques you'll optimize the tables to avoid redundancy. This method can help you understand which organizations to produce in line with the relationsships between your different fields.

You need to probably split the information in regards to a planet etc to ensure that the shared (common) details are in another table.


Common (Table)
  Diameter (Column)
  Mass (Column)



Poor posts I understand. Possess the Planet and Comet tables connect to the most popular data having a key.

This really is certainly a subjective question. It may sound as if you happen to be around the right lines of thinking. I'd request:

  1. Do these objects share many characteristics? If that's the case, it's most likely worth thinking about at the minimum basics table to list out all of them in.
  2. Do you object "extend" another - it's all of the characteristics from the other, and several extra supplies? If that's the case, it may be worth adding another table using the extra characteristics along with a one-to-one mapping to the bottom object.
  3. Do both objects have numerous shared characteristics and unshared characteristics? If this sounds like the situation, maybe you'll need a single table along with a "data extension" system where each object may have a type or category that identifies anywhere of additional characteristics that might be connected by using it.
  4. Perform the objects only share a couple of characteristics? Within this situation, they're most likely different enough to split up into multiple tables.

You may even request yourself how you will query the information. Are you going to ever would like to get all of them within the same list? It certainly is smart to mix data into tables along with other data they'll generally be queried with. For instance, an "accessories" table in which the file is definitely an image or perhaps a video, rather than images and video tables, should you generally wish to query for those accessories. Don't split up into multiple tables unless of course there's an excellent reason.

For a moment ever would like to get planets and comets in a single query, they'll virtually need to be within the same table if you would like the database to operate effectively. Inheritance ought to be handled within your application itself :)