asiby wrote:
Good stuff Sam.
I am sure that you contribution a welcomed and appreciated.
As for the 2 additional tables I was thinking about another approach. I would have just added 1 field to the cc_card table. And that field would contain data looking like : 2,3,12
Where 2, 3 and 12 will be the id of the selected recurring services.
And to match any services in a SQL statement, I will either one of the following:
SELECT ..... FROM ...... WHERE ... recurring_service_id IN (cc_card.recurringservices)
or
SELECT ..... FROM ...... WHERE ... AND FIND_IN_SET(recurring_service_id, cc_card.recurringservices)
The reason why I would have done it this way is that when you delete a card, you won't have to bother cleaning up the other related tables. And it will keep the SQL queries lighter.
I may be wrong. Let me know when the changes are done and how user friendly it is
Your way is every simple and sweet and would probably be all that is needed to roughly 95% of those needing this feature. I think for a quick solution that is the way to go. I will take a closer look at it if the way I was doing it turns out to be too costly. However it doesn’t follow the basic way Areski is using when he groups things. This is clear by looking at how the tariffs were grouped. Unless I am wrong, by just adding one field in the cc_card table using the 2,3,12 approach you might limit scaling a little and usefulness to some degree. For instance A2b works well as a billing application on top of other applications like freePBX. (Better then most people know) I will talk more about this project later. This opens up a lot more services one will need to bill for. Suppose someone wants to offer service packages similar to the local LECs and charge for various features like callwaiting, caller Id, three way calling, voice mail and the many other features instead of packaging it all in the monthly bill. Trying to do this all in one field might get cumbersome 2,3,12,4,6,7,9,15. Ect,
asiby wrote:
The reason why I would have done it this way is that when you delete a card, you won't have to bother cleaning up the other related tables
I don’t think I explained it right. There will be no cc_card related records in the 2 new tables so there is no cleaning up if you delete a card ID or client.
Look at how the grouping was done in the tables cc_tariffgroup and cc_tariffgroup_plan and cc_tariffplan. The same principle is applied here with cc_service, cc_service_group, cc_service_group_plan. Hypothetically one will be able to create 100 or more types of recurring services and package it in 10,000 or more different ways with each packae having a special name (of ones liking) for that group. Only the cc_service_group ID has to be added in the cc_card table. All the recurring services under that group id will apply to that Card account.