I have a subscription database which I need to pull data from on a regular basis and I'm thinking about building a module to simplify the process.
The two tables I'm interested in are the Customers table and the Subscriptions table. The Customers table contains information such as name, address and so on. The Subscriptions table contains data such as the subscription start date, end date, start issue number, end issue number, publication name. The two tables and joined using id fields (Customers::id = Subscriptions::id_Customer) and the subscriptions table has 50,000+ records.
The data I need to pull can vary. Here are some examples -
- All customers who are currently subscribed to publication A
- All customers who are subscribed to publication A but have NEVER subscribed to publications B or C
- All customers who have NEVER subscribed to publications A, C or D.
- All customers who have subscription to ANY publication in the past but are not currently subscribed to ANY publication
I've attached a screenshot of the UI that I have in mind to build the query.
So my question is this... should I construct an SQL query or will I get better performance using native Finds and then Extending / Constraining the Found Set?
I'd appreciate any thoughts or input on this.