Storing contacts

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:

“For more info, please contact Bob Jones at bob.jones@foo.com, or Beth Smith at beth.smith@bar.org

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:
name|email;name|email;name|email

But maybe there’s a better way.

Normalize your database.

####Users
id (PK) | first name (IX_1) | last name (IX_1)

####User Emails
user_id (PK/FK) | email (PK)

This way, you can have a 1 to many relationship. You don’t necessarily have to define FK restraints.

Edit: I may not have understood what you’re trying to do. At first I thought it was 1 user with multiple emails… I’m not sure what you’re trying to do now that I re-read it.

Are you trying to attach multiple users as contacts to single courses?

####Courses
id (PK) | coursename

####Users
id (PK) | firstname | lastname

####Course Contacts
course_id (PK/FK) | user_id (PK/FK)

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.

(WHEW!)

Did I get that correct, @cydewaze?

@WolfShade is 100% correct. I currently use listfirst() and replace() in ColdFusion to make a pretty format out of each address (<a href="beth.smith@bar.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.

Ah ok. Right.

JSON Strings then. :slight_smile:

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.

{email:name}

That way

{"bob.dole@pres.com":"Bob Dole","bill.clinton@pres.com":"Bill Clinton"}

Or if you have nulls:

[{email:"value", name:"value2"}]

So

[{email:"", name:"Bob Dole"},{email:"bill.clinton@pres.com", name:"Bill Clinton"}]

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.

1 Like

DeserializeJSON supports JSON arrays, and can even detect if a JSON object was originally a query and can automatically return it to a query object with one optional argument set to false.

DeserializeJSON()

:slight_smile:

1 Like

This sounds like a fun way to go! I’ll do some more reading, thanks!

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.