MySQL – Handling Many-to-Many Relationships Between Multiple Tables

database-designeavmany-to-manyMySQLsubtypes

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?

Each body type has its own table
Each car in each body type table can has several features

As you can see from my "schema", each car can have several features but I need to connect features table to several car body types tables (many to many relationships). I've thought about it for several days but Im still not sure how to achieve this.

Why all body types are in separate tables? This seems to follow normalization rules, it should also speed up queries because I will never have to query several body types at once.

Is it possible or should I rethink it? Should I just make another table about body types and combine all the cars? I expect to have a maximum of 1M entries (all body types combined). There's going to be a lot more reads than writes.

Best Answer

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .

Related Solutions

Mysql – Full text search through many tables in MySQL

Sure, it's fine to copy the searchable content to your full_text_search table.

MySQL supports FULLTEXT indexes only in the MyISAM storage engine (until MySQL 5.6, but fulltext in InnoDB in MySQL 5.6 still seems a little unstable). So you can store your canonical data in InnoDB for safety, and a copy in MyISAM for indexing. MyISAM is susceptible to data corruption, but if it's only a copy then you just need to repopulate the MyISAM table if it ever gets corrupted.

Your use of master_id as distinct from the primary key of each table is a little strange. Why not use the primary key, and add another column to your full_text_search table for the type of content?

CREATE TABLE full_text_search
(
    id INT NOT NULL,
    content_type ENUM('news','polls','comments','galleries','articles') NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( id, content_type )
);

full_text_search table:
+----+--------------+---------------+
| id | content_type | content_text  |
+----+--------------+---------------+
|  1 |         news |     ...       |
|  1 |     articles |     ...       |
|  2 |     articles |     ...       |
+----+--------------+---------------+

Another option is to create a fulltext search index in another specialized technology such as Sphinx Search or Apache Solr. But the same pattern would be useful -- store the primary key field and a field for the type of content.

Database Design – Managing Many One-to-Many Relationships Between Two Tables

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).

Best Answer

Related Solutions

Mysql – Full text search through many tables in MySQL

Database Design – Managing Many One-to-Many Relationships Between Two Tables

Related Question