I’m trying to build a recruiting tool and basically the way it works is there is a table that holds all of the possible recruitees. A user is able to build different sub-lists of out of that master recruiting list based on different criteria, and I already have the logic to make that work. After the list is built what I want to do is be able to save the list. A campaign is built by combining various lists together and then the recruiting material is distributed to the recruitees. I’m struggling with the database design in order to do this. I’m not sure what the best implementation for the different lists would be. I was thinking initially I would save the lists as separate CSV files that can be pulled in and out of storage and combined programatically but all of the recruitees are already stored in the DB so why go to the trouble of building separate CSV files and move them in and out. If I represent the list as a table that seems like it would add a ton of tables to the DB. I’d love to bounce ideas off of people on the forums here that have a lot more DB design experience than I do. I feel like it has to be a pretty simple solution I’m not seeing. Thanks!
Hello!
I would use a simple string to store the lists as JSON 
. It may be a simple table with key-value or an in-memory database like Redis. As long as they don’t require data integrity (what happens if a recruit leaves/deletes the account?), then it’s not really important where you store it.
It’s up to you in the end though
Hello @skaparate Sorry this is the sofa king just using my work account. I’m not terribly familiar w/ redis or even nosql databases. Do you think this sounds like the right way to go in this situation? The idea is that I’m going to have a bunch of lists associated w/ a campaign which would be represented in the relational database as a campaign table. How would I connect those lists w/ the correct campaign? Would it make the most sense to use something like mongo to save the lists and then pull them in and out of the db for manipulation but have them be associated w/ the correct campaign somehow? Sorry I’m still new to a lot of these ideas and would love your guidance! Cheers
I may have misinterpreted what you meant here:
If that means that the criteria may vary and requires a lot of customization, then a JSON field in another table is a good option.
If, on the other hand, you only need a reference to the recrutees in another table, then this should work:
table user_list_recrutees
-------------------------
recrutee_id -- A reference to the recrutees table
user_list_id -- A reference to the user_list table
table user_list
---------------
user_id
campaign_id
Hi @skaparate,
Thanks again for the help!
Basically the table right now is formatted like this:
table recruitees
-----------------------------------
name
country
address
phone_number
business_phone
email
company_name
What will happen is the recruiter will take that data from recruitees table of about a million users and create sub lists that have all of the same column info. With those sub lists, I’ll need to combine the sub lists depending on the campaign the recruiter creates. For example if I create sublist_1, sublist_2, sublist_3, sublist_4, sublist_5 (each sublist is filtered based on different criteria) I could for example create campaign_1 that includes sublist_1 and sublist_3. I could then perhaps create campaign_2 that includes sublist_1, sublist _2 and sublist_5. Once those campaigns are created I will need to take each sublist for each campaign and modify and format the phone columns to pipe it into recruiting software. Does this help? Thanks again for taking the time to help me, like I said this is a challenge I’ve not had to work on before and my knowledge of backend solutions is really limited.
In that case, the structure I proposed before still applies 
.
I would structure it like this (including your definition and my last reply):
table campaigns
---------------
id
user_id
table campaign_lists
--------------------
campaign_id
user_list_id
It may seem hard to understand and query, but it’s one structured way of doing it. This has the benefit of data integrity in the form of foreign keys.
Good morning @skaparate,
I thought about your design and here’s what I have as a working model and would love your feedback.
Thanks!
That’s what I thought of too and should work , though I don’t see the user table (that should own the recrutee_lists).
@skaparate I think I’m going to just have all users have access to all of the campaigns so they don’t have to be owned by a single user. The one thing I’m not sure about though is how to implement CRUD operations on a many to many table structure. I’m doing some research now but I didn’t consider it’s not as easy as a one to one or one to many structure.
An ORM should help with that, though it’s still somewhat problematic.
I guess it’s not that common in practice to implement that table structure? I’m not finding many resources about it. I was trying to write this project using vanilla php - I was connecting to the DB using PDO, but this is like the only resource I could find that highlighted the solution to the problem. His code is kind of confusing to me so I’ll just have to study it for a while to get the hang of things.
https://www.tonymarston.net/php-mysql/many-to-many.html
I suppose if I went the ORM route I could hook up something like doctrine?
It isn’t that difficult, it’s just tedious to write in vanilla PHP (iterate rows, query for existing results, validation, etc.).
The steps to store a new list would be:
- Have a list of all the recruits (not all data, just the IDs).
- Insert the list first (you need the ID for the link table
recruitee_list_xref). - Insert the ID of both in the
recruitee_list_xref.
<?php
$list_name = $_POST['list_name'];
$state_type = $_POST['state_type'];
$recruit_ids = $_POST['recruit_ids'];
// You should validate this data, of course :)
$con = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
// check that the connection is valid, etc.
$query = "INSERT INTO recruitee_list (list_name, state_type, created_on) VALUES (?, ?, CURRENT_TIMESTAMP)";
$list_id = -1;
if ($list_stmt = $con->prepare($query)) {
$list_stmt->bind_param('ss', $list_name, $state_type);
$list_stmt->execute();
if ($list_stmt->affected_rows != 1) {
echo "Error: more than one list or no list was created";
} else {
$list_id = $list_stmt->insert_id;
}
$list_stmt->close();
}
$reference_ids = [];
if ($list_id) {
// Here comes the tedious part...
// First, you would need to have an array of recruit IDs
// In this case, I'll assume it's just a comma separated string of IDs
$ids = explode(',', $recruit_ids);
// You need to validate the IDs, as it's user input... I'll skip it though :P
foreach ($ids as $id) {
$query = 'INSERT INTO recruitee_list_xref (recruitee_list_id, recrutees_id) VALUES (?, ?)';
// This will iterate the IDs and insert each one, appending them to the reference_ids array:
if ($ref_stmt = $con->prepare($query)) {
$ref_stmt->bind_param('ii', $list_id, (int)$id);
$ref_stmt->execute();
if ($ref_stmt->affected_rows != 1) {
echo "Error: more than one reference or no reference at all";
} else {
$reference_ids[] = $ref_stmt->insert_id;
}
$ref_stmt->close();
}
}
}
$con->close();
And that’s only to insert the list and referenced recruits.
I suppose if I went the ORM route I could hook up something like doctrine?
Yes, any ORM should work, since most will help you skip the query creation and some validation. I don’t have experience with PHP ORMs though 
.
@skaparate Thank you so much - this was extremely helpful!! I seriously can’t believe how awesome and patient you were with this. Please let me know if there’s ever anything I can do to return the favor!