Is the concept of abstraction relevant to tables in MySQL? If so, how can I do it?

Question

I want to store data on various engines in a MySQL database, which includes both piston and rotary engines.

---

In OO languages, I can create and extend an Engine superclass to obtain PistonEngine and RotaryEngine subclasses.

The PistonEngine subclass would contain properties such as CylinderNo, PistonBore and PistonStroke.

The RotaryEngine subclass would contain properties like RotorThickness and RotorDiameter.

---

In MySQL, while I can create two separate tables for piston and rotary engines respectively, I would prefer to maintain an EngineType field as part of the engine data, and store all data common to both engine types in a single table.

How can I design my database to avoid data redundancy as much as possible?

Answer 1

My suggestion would be to create a single table with all of the information needed for both engine types, including a key for the engine type itself. After that you could create a view for each type of engine "classes" so it would appear to be its own object. Although, depending on all of the data that you are storing, it may not make since as your structure would not be normalized.

Update Based on comment, I have expanded my answer.

By definition, view is a virtual or logical table which is composed of result set of a SELECT query. Because view is like the table which consists of row and column so you can retrieve and update data on it in the same way with table. View is dynamic so it is not related to the physical schema and it is only stored as view definition. When the tables which are the source data of a view changes; the data in the view change also. (http://www.mysqltutorial.org/introduction-sql-views.aspx)

Here is a link to a reference on creating views. Here is one more link.

Answer 2

I would create four tables in this situation.

One called EngineTypes which would just have the id/value pairs for your engine types 1 Rotary 2 Piston

One called Engines that contains the information that is relevant to both piston and rotary engines. It would have a column in it that contains the engine type id.

One table called RotaryEngineDetails that contains all your rotary engine specific data. It would have a foreign key to your engines table.

One table called PistonEngineDetails that contains all your piston engine specific data..