Unique Name Problem

I’ve been working on a real head scratcher of a problem for our site. We have a growing listing of books, along with all the connected information that goes with them (title, author, ISBN, publisher, etc.). So far, we’ve been using my own designed php/Mysql system to enter them on the computers in the office, and then upload in groups to go live on the web site. I set up an Apache/Php/MySql server to run on the office computer to do all the offline work. What we want to do now is allow our site customers, which are Authors, publicists, etc., to be able to enter their own book info online.



They sign in through our CMS we use, and then enter their book info using a Php/MySql form system. At first, I thought that this wouldn’t cause too much trouble. I even added a “final check” on the admin side so all the data is double checked before it goes live. The book data goes into the same tables as our current bookstore that we’ve been uploading to so it can be used in a variety of pages we already have.



My problem started when I decided to separate the Author names out of the original table, making the author table a many to many join to the book table. Of course I need a unique id field in both, but I found it wouldn’t work because what if there are two Authors with the same name. Author Jane Doe writes mysteries and goes to sign in. She enters her new books, then goes away to write more. Then another Author Jane Doe, who writes textbooks, signs in, goes to enter her new book, and sees that there is already a list of books under her name. This might still work as long as the books are ALL done using the online sign in and enter system, because the database will see it as two different people entering. Displaying the books is done by what category they are, so that even still works with two of the same name. The problem is we wont be able to enter info on the office system anymore because the database sees it as all coming from the same person. Somehow I need a way to be able tell the database the difference between Authors that we enter offline.

My first thought was when entering a book, after picking the Author, have a JavaScript onChange run a php program to check and see if there are more than one match to the name. If there is a match, pop up a window showing the names, and a short list of their books. That way whoever is doing the entering can select which name is the correct one. I just don’t like that method very much, mainly because we do a lot of entering, and it sounds time consuming. Also that means making the Author name an option list to pick from. Right now we have nearly 1000 different name in the db, which will make a very long pull down list. The only other option I have now is keep all the data in a single table. This is the way it is now, and I keep having problem with things like duplicate entries, and misspelled names.


I’m sorry for being so long winded, but I thought someone might see an obvious answer that I keep missing.

weird someone acutally used the font tags

Anyways the best way to fix this would be to check your online server when entering offline or create a 3 way sync (or something similar to CVS). Basicly when they enter online it doesn't go live right, well you could have it email you the data to enter (just a query would be all that is needed) or you could have it insert it online but then not go live and have your offline server sync to the online one and get all the data from there. etc.. ah I don't know what's the best currently I use a mail system, they enter data it's mailed to me I copy and paste and then I upload our database, that way items are always unique etc...

What's even weirder is I didn't set any font tags.

I had considered an e-mail system like you said. I was trying to stream line the operation, and I thought that would slow things down. I was thinking maybe if the incoming mail had the info attached in a "database ready" format like csv, that wouldn't be too bad. I still wish there was a way to have it done all online with us just OKing the entries as they come in.

like I said you can do that but you have to sync it with your offline server, and your offline one has to sync online, you can do a 3 way sync might work.

In other words, what you are saying is that your problem is your offline system, which you use for actually entering the Author info does not have a unique key? How does this system decipher one author from the other?

It would be a shame if it was a combination of firstname + lastname. I doubt it... whoever would put together such a system.

What are the database fields of this system? Let me know and I can help you figure out a solution.

If I had to take a blind stab at it, I would venture to say your offline system has some sort of unique ID. If this is the case, you should be fine. When you import the Author data, you simply will not use the MySQL auto increment id (assuming this is what you use now to generate a unique key for author), you will use the key your offline system is using.

What happens if they keys are not of the same type? Well, you have an issue... Is e-mail address a possibility for a key? This is a good alternative if this info is in both databases.

Yes the offline db uses a auto increment id field. Also you are correct that it's the offline one that's the problem. The reason is I never designed it to be used as a online entry system too. We were working on the problem last night and might have a solution. If I can take our current table of about 2000 book entries, and split out the Author names, while keeping them linked to their books through a common id field. That part I haven't figured out yet, but I'll get to that next. That will leave me with close to 1000 individual Author names.

In my offline form, I can change the first name/last name fields to a option group that's populated from the new author_names table. If in the rare case that there ends up with two identical names that are confirmed to be separate people, it will show up in the pull down option group. I guess from that I would have to run some sort of query that detects more than one match, and gives an extra way to select which name is the correct one for the book we're entering. My first thought would be a pop up that shows both names, and a short list of their books. From that a simple check box could set the correct name then continue the entry. It just seems like an awful lot of work for something that might never come up. But I know that those are always the problems that will come back to get you later.

I don't suppose anyone has heard of a utility to split up MySql table that have data? If there isn't I need to do this asap before we fill it up even more

From the sound of things, it seems you have no intention of taking down the offline system. Am I correct?

I was hoping this data export and import was only a one time deal.

As for splitting the MySQL table, the easiest thing to do would be use SQLyog or EMS MySQL manager to get a DDL view of the create table statement for that table.

Copy that create table statement into a text editor, copy it, then cut/copy/paste the fields to and from the two table definitions until you are satisfied with the results.

Make sure you have reference keys to relate. This should be a fairly painless process.

BTW -- if you do not have EMS or SQLyog, you can get dump the DDL for a table using the mysql command line client. For example, using an oscommerce database, I could get the create table statement for the products table with the following command:

mysql> show create table products\G;

You definately want to add the \G after the table name as the results will be more legible.

The above statement:

I'm afraid that I can't loose the offline db, it's the way most of the boos get entered. I've been able now to separate the whole bookstore table into three separate tables. A book table to hold just the book info. Author table to hold author names and a unique id. Then a book_author table that has the ISBN and the author id to match it (and a unique id field). I didn't have any utilities to help do the splitting, so I wrote my own php scripts to do the trick.

I think I'm going to experiment with a new entry form that uses the author name pull down option list technique. I need to see how much it slows the form down compared to just typing in the first/last name in a text field. I still haven't worked out how the form will detect when there are duplicate names in the list, and then how it will respond. At least I'm a bit further on now.