Create Database for data

[tskeeley]

Create Database, MySQL, with relational data to Neon Account info for all non-core data.

Include PDO extensions for database use (consider making PDO filter for neon)

[tskeeley]

Convergence Events Portal Master DB.pdf

First draft - please review and respond @AricStewart @seanbseanb

[AricStewart]

Points I notice:

• Some departments have multiple e-mails, You plan to seralize the into a single string (ugly) or have subtable (more db centric)
• ConCom List does not have dates served, could be useful for tracking years served etc.. Even just a INT of CVGYear would be enough
• Volunteer Prizes want a quantity also? Help track when items 'sell out' at the end.
• Nothing hotel based here yet...
• Volunteer T-Shirt It is a prize with a min but does not spend hours (right?) Maybe a field in the prize table with Value and Cost? Or a Boolean for 'Costs Hours' or something.

That is from my first pass this morning.

[tskeeley]

Thank you!

• Subtable makes sense, I'll update
• Tracking years make sense. I'll have to figure out how to parse it and if it's worth abstracting, in case we do multiple events (or wish to support other cons doing so)
• Will add quantity
• Lottery change, forced by 2018 hotel vendor, hotel support on serious hold.
• I'll look at tshirt issue, you're right that we need to account for it as a zero.

[tskeeley]

So, here's the latest version, the one I am working with and building out. A few notes:

• Tracking year was a great idea all over! We'll need a function to add/remove a conyear, and can't remove if it's in use.
• Item quantity is total available, basically, the amount we purchase minus broken/loss. Need a function to "true up" quantities during an inventory in case of loss/change.
• TShirt - it's going to have a zero-hour value, and we need to have the code deal with filtering it from people that don't have "X" number of hours (currently 15). The problem is, we also need to handle if someone already got one or not and not allow them to get a second one. So, I need some ideas on this. Do we want to have a seperate table to just handle tshirts as "non-cost swag" helpful if we ever decide to hand out something else like a tshirt.

Convergence Events Portal Master DB.pdf

[AricStewart]

Oh more comments as I look:

• You have an e-mails table that does not appear connected anywhere. Is that to try to address the multiple e-mails for a department?
• Maybe have 2 types of volunteer prizes... One-time prizes and hourly prizes (Not in love wit those names). Then you can keep the hourly prizes like you have with an hour redemption table but then also have a one-time prize claimed table that looks the same but refers to the one-time prizes?

[tskeeley]

Yes, the email is to link to the places we need departmental emails. Assuming I can find a true root for all user emails... but we'll see about that later. :)

Hmmm, I might be able to work with the hour vs one-time rewards. I will have to add a type field so we can sort/filter on it, unless you want it in a seperate table. hmmmm, I can go either way on it. advantages to both, really. But... wait. they serve different functions, so I don't want to limit the options by joining tables. I'll add one. See? solved! :)

[tskeeley]

Got it!! Here's the latest. I added a new table for rewards

• Promo - The value of the item is not deducted from the volunteer
• Group - Assign a reward to a group that is shared between items. M-TShirts are the "same" as L-TShirts - This is optional. If null, no limit or cross-item restriction is in play. Note: a group can exist with one or more items.
• Limit - In this group, someone can only have X number of them

So, NORMALLY, Promo would be False. A keychain takes three volunteer hours, so when you get one it "costs" three hours. If Promo is not True, then the item shows when the member gets the right value of hours, but if they redeem, it "costs" zero.

We can assign the promo/free item (or any item!) to a group. That way, if I redeem a XL-TShirt, I can't ALSO redeem a L-TShirt as that is technically a different item. And we can limit it based by using the limit field.

Convergence Events Portal Master DB-v4.pdf

[AricStewart]

You see the groups coming into play in any place other than the Promo items?

If you have red foozles and green foozles but they are not promo items then it does not matter if they are tracked in a group, right?

If groups only have to do with Promo (freebie) items. then maybe we can move the Promo field out of the item and instead have it be "Promo Group" and the presences of a "Promo Group" field means that it is a promo item. It does mean that for promo items that are solitary then we would have a whole table for them with a single entry. But I don't think that is going to be a big deal.

Thoughts?

[tskeeley]

You are right. But, there was one situation that happened that this would support. I'm willing to be talked out of it, but let me explain first.

Situation - We have 300 copies of four special movie posters. They "cost" two volunteer hours. But we are giving only one of the four to each person so people can get a chance.

Another possible situation is like the uber gifts (the "really cool duffle bags"). If it was decided that people could only get one of them.

Again, I am willing to be talked out of it, up to you. Let me know, and I'll follow your lead! I'm not married to the idea. :) I was just so excited! LOL!

[AricStewart]

Ah, cases I had not realized or considered.

Your movie poster idea is a good example.

To generalize, if there is a time you want to limit the number of items or number of items in a group to a given number regardless if they are 'Promo' items or not.

ok I am liking this current design more then.

• So normal, unlimited non-promo items would have 'Promo' as false and a null group.
• Promo items have a built-in limit of '1' unless part of a group
• Grouped items can only have the number of items claimed out of the group up to the group limit.

ok i am on board.

I would maybe changed 'TotalPurchased' to 'TotalInventory' Purchased i find a little confusing as to if it is purchased by the con into the supply of 'purchased' by volunteers from the supply.

-aric

[tskeeley]

A few updates -

LucidCharts (oh my gosh I love them) allowed me to export SQL from the map I made. So much time saved but it caused a few updates.

Added a buildDB function that initializes the DB. I figured it would make sense to see if the DB was there before slamming it in place, but I wanted to check with you first - do we want to only check the DB existence if it doesn't exist? IE: Use a "try/catch" and if the error for run is "no such table", then build the DB? Problem with that is it is not elegant at all and will messy fail first run.

So, my other option was a manual switch, but now I am deep into the install routine we brushed against, and you seemed to have a good idea for that!

So, I am going to check this in, and ask you to review it, and then let me know what you think we should do next for the instantiation.

Convergence Events Portal Master DB-v5.pdf