I’m converting an excel spreadsheet into a database that I’ll be using to build a simple web application. The spreadsheet/database is basically a list of online courses, with specs for each course, as well as a topic, category, etc.
One problem I ran into was the contact for each course. In the spreadsheet, I found the following:
The first thing I did was move these email addresses into a column, delimited by semicolons. Since all of the current email addresses are in firstname.lastname@ format, it’s easy to make a decent looking name out of it on the page, but I fear that this may not always be the case.
What’s a better way of storing name/email address pairs when there are multiple contacts for a record?
I thought about something like this:
I think what the OP means is that the spreadsheet currently has just email addresses separated by semi-colons, which makes it easy (I assume using split() or some such) to derive a full name for said address based upon the fact that each email address is currently in a “first name dot last name @ domain dot TLD” format… but OP is concerned that this will not always be the case, and is looking for a method of storing a full name WITH the email address so that OP won’t be forced to devise different methods of extracting said full name from email for multiple formats.
@WolfShade is 100% correct. I currently use listfirst() and replace() in ColdFusion to make a pretty format out of each address (<a href="firstname.lastname@example.org">Beth Smith</a>), and cfloop to spin through the list of addresses using semicolons as the delimiter.
I was trying to avoid having a users table, because the updates for this will be coming in an Excel spreadsheet format. The person updating the list will be the content owner (web-saviness questionable) and she’ll be pasting from the spreadsheet into a form, then ticking checkboxes for things like the categories and topics (because they’re in separate tables so I can do a proper many-many relationship).
If the users are in a separate table, she’d have to check to see if her user is already in the database (some users are contacts on more than one course, some are only contacts on one), and if not she’d have to add them. I have a feeling users will just be pasted in without checking, so I’d have to build an existing user check into the back-end app. Not to mention remembering to remove users who no longer have a course assigned to them.
I guess I was hoping there’d be an easier solution.
Right, I understand. This would be a fun smart system to build, but probably not in your time constraints. If you store it as a simple JSON key/value then print it on the screen in a readable way since JSON is easily used in either JS or DeserializeJSON() in CF, then she could manually verify them.
I can’t remember how well DeserializeJSON() supports JSON Arrays… but I do remember there being some weird issue though. It might have been fixed. In any case, I’d probably just print it with JS just for the sake of being easier and faster on me.