0

BACKGROUND:

I am developing a search feature that enables users to search three entities at once: classes, student organizations, and events by name.

MySQL 5.6 and Innodb will be utilized on a relatively small server.

TABLES:

Search

entity_id  (tiny_int)
entity_type  (tiny_int)
full_name   (varchar(255))

Index (Primary) -> entity_id, entity_type
Index (FULL TEXT INDEX) -> full_name

Class (entity_type = 1)

class_id
ALL OTHER COLUMNS...

Events (entity_type = 2)

event_id
ALL OTHER COLUMNS

Orgs (entity_type = 3)

org_id
ALL OTHER COLUMNS

QUESTION:

Is it appropriate to index the name of 3 seemingly similar but different data sets (class, event, organization) into one search table through the use of entity_id, entity_type Primary Key?

How would I go about doing a join on the search table with the Class, Events, Orgs tables? Is there a conditional I can do using the entity_type?

Any help or guidance is greatly appreciated.

ProfileTwist
  • 1,524
  • 1
  • 13
  • 18

1 Answers1

1

This seems like a reasonable approach to linking these three table types. It's v similar to what you often see ppl do with 'account_type: user, admin, other' and links to separate settings for each.

To do a join between the main table and all three subs will depend on how similar the columns are. If they are v similar you could do three joins in the same query and put the entity_type as part of the on clause. If they are v different then you may be better served by splitting up the query into 3 distinct joins. You could union these together but it may or may not be worth it.

ethrbunny
  • 10,379
  • 9
  • 69
  • 131