MLM Database table structure

[levonk]

I am creating a list for several online journals and I would like people to subscribe/unsubscribe. Most people will want to subscribe to more than one journal. I am using a WordPress site for the subscription. The widget works fine, but for every journal someone wants to subscribe, they need to enter all the information again.

I would like to have a page, instead of the widget, where personal information can be entered, and checkboxes with available journals so people can tick the ones they want, or un-tick the ones they don't want.

The way the tbl_mlm_sub table is setup will not allow this. It also multiplies the number of records, hence the database size, which may become quite large when you have many people subscribed to many journals.

I know this would be a big job, but is there a way to create relational tables to hold the information required? My guess is there would be need for three tables: 1. Personal Information, 2. Journals, 3. relationship table.

I am also creating a FileMaker solution to manage the database and would be happy to share that. But I am unsure whether it is worth working with the current database structure. If the possibility of a change of structure exists, I would rather work on that.

 

[stanbusk]

You can whatever with MLM, it can be a starting point for a custom system. You will have to find a PHP/mySQL developer though.

Note that current MLM comes with sample subscription pages that allow you to subscribe to several lists at once.

 

[levonk]

The way the database is structured, it is not easy, and takes too much work, to implement any kind of proper solution.

The DB needs a typical many-to-many relationship but is setup in one table: tbl_mlm_sub.
If that table is divided into two, and a join table is added, then would be easier to implement.
One way would be to remove the fld_list from the tbl_mlm_sub and have a new table with that field.
The three tables could be:
tbl_mlm_con: This would be the same table as the tbl_mlm_sub without the fld_list and fld_con_id instead of fld_id.
tbl_mlm_list: This would contain the fld_list and fld_list_id fields.
tbl_mlm_sub (the join table): This would contain fld_sub_id, fld_list_id and ld_con_id.

That way, each persons record would exist once, and modifying would be done in one record. Subscribing to a new list would simply mean adding a record to the join table.

As it stands, it takes a lot of processing to make small changes to the data, whether I use PHP or a MySQL shell.

It would mean a change to the lm scripts and functions, but would make things much easier to work with.

An alternate is to have the fld_list field hold all the subscribed lists as a comma delimited text without changing any of the tables. PHP and SQL can easily separate the field for manipulation.