For external data, should I create custom table or new entity type?

Question

I have a database that exists outside of Drupal. I wish to show the summarized data from that database on Drupal.

At a high level, you can see the structure of the data below.

| Seller's ID | Product Category | Date      | Total Sales |
| 1           | Widgets          | 5/1/2018  | 20000       |
| 2           | Widgets          | 5/1/2018  | 40000       |
| 3           | Widgets          | 5/1/2018  | 50000       |

In the past, I simply have made a custom table in Drupal and then performed SQL queries on that table using a custom module.

Within this table, I likely have hundreds of thousands of rows. As far as I can tell, I have two viable options:

1. Entity Type - Create an entity type with fields that mirrors the columns that I expressed above.

   • Potential benefits:
     I should be able to use the full power of views to create different reports. I could also make new reports from data without having to touch the custom module.

   • Potential Disadvantages:
     I have concerns about site performance. On a prior site, I had hundreds of thousands of nodes and it seemed like it substantially slowed down the site.

2. Custom Table:

   • Potential Benefits:
     1) This would be easy to implement on the front end
     2) I would not be adding lots of nodes that MIGHT negatively affect performance.

   • Weaknesses:
     1) I won't be able to use the data without leveraging a custom module to query the SQL.
     2) Only developers with knowledge of creating custom modules would be able to create new representations of data.

Overall, I would prefer to create an entity type to hold the data so I can deal with the data in the front end of Drupal. With that said, I don't want to set myself up for disaster in terms of performance down the road.

Should I be worried about the database having hundreds of thousands of entities in the entity table?

Are there other options I am not aware of?

Answer 1

It's really up to HOW you are going to use it. Just listing it? Will you extend it with new fields? How likely will be problem be more complex in the future?

Custom entity is a definitely good thing: it's not just about views, but lot more (entity hooks/events for example). Don't worry about performance yet, since you didn't mention anything that could be dangerous. Still regarding simplicity the table solution would be still a winner.