I’m in the process of going through all of our old code and cleaning up things that were not done optimally the first time around. One of the things I’m working on now is a database that holds all of the details for each of our web pagespages (title, contact ids, whether it’s a recent item, featured item, etc).
Originally each page could have only one contact, so there was a column for contact ID. As time went on the need for a second, third, on up to sixth contact person arose, so a column was added for each. The problem is, when that happened, the staff directory table was joined again for each of those, so now we have it joined six times for each page, whether that page actually has all six contacts or not (most have two).
Even with that, the query execution time (uncached) is between 16 and 31ms, which doesn’t seem terrible.
But, I’m thinking that it’s still pretty clunky, so I’m thinking it might be better to have a new table that relates page ID to contact ID and just join it once. That way, no only do I lose 4 joins (joining the page table to the new table, then the new one to the staff table = 2, rather than the 6), I also allow an unlimited number of contacts per page, which seems like it would be a lot more future-proof.
Yup. Basic normalization. Now, with query times of 16 to 31ms, you’re not going to actually physically detect any kind of increase in speed, and it may actually not bring it down much less than that. But it should be a much smoother query.
I also just realized that on my new table I’ll have to have a third column, because the order in which the contacts appears is important. With the current method that’s easy, but I think I’m going to need an order column for this to work.
[pageid][contactid][corder]
SELECT pagedetails, etc
FROM pages
INNER JOIN page_contacts
ON page_contacts.pageid = pages.pageid
INNER JOIN contacts
ON contacts.contactid = page_contacts.contactid
ORDER BY corder
That should give me contacts in the proper order. The only annoying part will be reordering them all when one changes.
Actually, if you use jQuery-UI, there is a “drag n drop” ability. We use it on one of our pages. The downside being that every time you “drop”, it submits the whole order to the database. But it’s NICE and easy. (Not the hair-colouring product.)
