restructuring database

swissmissflash

Hello,

I have a database with a large sum of rows (12million+). I want to restructure a bit but i'm not sure how to do it or if this is the best idea. Hoping for some input from you smarter peoples. :p

Currently I store peoples URL's that users input without checking if the URL is already in our system. So we have many duplicates of the URL linked to different users. What I would like to do is have a URL table and then a 'link' table. So before I store the URL i would check if it's in our system and the 'link' table would store the id to the URL instead of keep filling our database with duplicates.

So for example (here is our current setup)

Table: URLs
id, user_id, url_title, url

What I would like to happen is this:

Table: URLs
id, url

Table URLs_link
id, urls_id, user_id, url_title

Does this make sense? How might I do a select/insert statment to convert the dat a to the new table structure? I know I would have to do a DISTINCT() select for URLs but I'm kinda lost on how to make the switch to the new system.

Hope this makes sense. 🙂

Thank you!

bradgrafelman

Well, it sounds like you're trying to do some database normalization. The problem is, you're probably not going to get 100% normalized since you probably don't have uniform data.

What I mean is, let's say User1 and User2 both like Google, and they want to add it to your DB. User1 is rather lazy, however, and always types in "google.com" to visit Google, so this is what he enters into your database. User2 however, is somewhat meticulous about everything he does, and therefore submits the full URL - http://www.google.com/ .

As it is, my suggestion that follows will not realize these are the same URLs (essentially - assuming 'www' is the same as having no subdomain). Beyond my following suggestion, I don't know a good (aka accurate) way to comb the database further and eliminate duplicates in such situations.

Alright, here's the queries I would do to achieve what you're wanting:

INSERT INTO URLs_new (url)
    SELECT url FROM URLs GROUP BY url;

INSERT INTO URLs_link (urls_id, user_id, url_title)
    SELECT new.id, old.user_id, old.url_title
    FROM URLs old
    INNER JOIN URLs_new new USING (url);

DROP TABLE URLs;

RENAME TABLE URLs_new TO URLs;

Now, let me say a few things about these queries:

I don't have time at the moment, so these are all 100% untested queries - I have no clue if they will work or not... I just think they will :p
You have a lot of rows, so this is probably going to be an expensive process. If speed is important, you may want to wait for one of the DB gurus to critique my queries (and probably tear them to shreds in the process :p).
As in point #2, this will be a very expensive process. As such, you may want to consider either: a) taking your site offline briefly(?) until the process is complete, or at least b) locking the tables.

Again, I'll caution you - I'm no DB guru... just a PHP coder with an idea :p

Piranha

Normally I am all for normalizing databases as much as possible. However, in this special case I don't think it is the best thing to do. As bradgrafelman have said there may be several ways that an address could be inserted, and it is hard to handle. But even if you manage that I am not sure about this. It all depends on why you want to change the database structure.

If you want to change structure to get it normalized I see no reason to do so. It may in fact already be normalized for your use. More about that below.

If you want to save space it is not worth it. If the addresses have a length of 50 characters, if you can remove half of them and they are in UTF8 then you would save 600MB by doing this. That is not even a full CD. Even if you could remove 95% of the addresses it would not be more than the data of 2 CDs saved.

If you have some other reason that you want to change it may be worth doing it. Without knowing it is simply impossible to say.

But if you change the design you have to join tables, and it may be expensive and take lots of time. And you need to do more queries to check if the address are already stored, that takes computer time as well.

I said above that the database may already be normalized, although there might be several rows with the same url. What did I mean with this? I ment that if the url is associated with the user the database may already be normalized if one user can't insert the same url more than once. If we compare this with a address register it would mean that we have to have one table with company id, another with street (yes, more than one company may be on the same street), another for the street number, one for city and one for zip. And tables to connect those with the company table. Then we only have a very simple register to basically send letters. With phone, contacts, email, fax, and other information it would be even more tables.

I have to admit that I am no database guru either, but I consider myself pretty good at databases. I might be wrong that you already have normalized tables, but I don't think so.

NogDog

Since the objective appears to be to prevent a given user from adding the same url multiple times, you could keep the one table but add a unique index to the the user_id and url columns, so that no duplicate combinations can be added.

ALTER TABLE `URLs` ADD UNIQUE INDEX (`user_id`, `url`)

You could then modify your insert code to specifically check for (msqyl_errno() == 1062) to see if a user/url entry already exists error has occurred.