Welcome Guest, Not a member yet? Register   Sign In
MySQL - handling multiple rows (inserting or updating)
#1

[eluser]TheFuzzy0ne[/eluser]
Hi everyone. Please accept my apology for the title of this post being so vague. I honestly have no idea of a better suited title.

I have an organiser application, which allows the user to keep track of certain activities they have done. By default, the user has no rows in the database, but could potentially have up to 46. The information for the tasks is stored in a separte table.

I'm not entirely sure how to handle the MySQL statements. If I populate the database with 46 rows the very first time they use the app, I can update them accordingly, but that will leave lots of rows that contain the number 0 (meaning not complete), which to me seems like a waste of space.

If I don't do it the above way, I can't see any simple way to update the rows that need it and to insert the ones that don't exist.

The only other option I can think of, would be to delete all of the user's data, before inserting the new rows, but I don't think that this is too smart, especially when you consider the overhead it may cost.

All of my ideas set alarm bells ringing in my head, which is why I believe the method I need to use is none of the above. What am I missing?

I'd appreciate any pointers from any seasoned MySQL users out there. I've been using MySQL for a while now, but I've never really had to solve this problem before. I'd like to keep my database lean, tidy and cleanly designed. Is this a question of database design, or do I just not know enough about how to use MySQL?
#2

[eluser]elvix[/eluser]
if you think you need 46 rows, then maybe you do. the best thing to do is to set it up (make sure you index all appropriate fields, obv) and try it out. there's no downside to mostly empty rows in a table, they do tell you something, namely that no information exists -- sometimes, that's exactly what you need to know.

imo, you're best off going with the simplest, most straightforward solution to begin with. your understanding of your application will evolve over time, and you can always modify things when needed.

also, optimization is not everything. the value of the app, your time, your server hardware, etc. are all part of the equation. because we build a lot of stuff on our own hosting accounts, we sometimes forget that better hardware is often the answer (dirt cheap compared to your time, in most cases). the cost of your time in developing and managing the app is huge too. don't make things so complicated that the app is too costly and/or difficult to manage.

in any case, that's my opinion. Smile
#3

[eluser]TheFuzzy0ne[/eluser]
Thanks for your input. I do seem to have an undeniable nack for overly-complicating things. I am a big fan of simplicity, and I know what great things can be achieved by keeping it simple. The problem comes when I try to make my applications "futureproof". For example, I would be adding more complexities at a later date if I decided to add another event to the list. I try to think ahead as far as I can, but I think my problem is I don't know where to stop. I try to anticipate every possible outcome, and that's what ultimately makes me end up shooting myself in my foot.

Thanks again for your input. All other comments welcome.
#4

[eluser]Armchair Samurai[/eluser]
If this is purely a MySQL app, you might want to look into the INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE statements - from your description they might go a long way to solving your issue.
#5

[eluser]TheFuzzy0ne[/eluser]
I had briefly looked at "ON DUPLICATE KEY UPDATE", but it seemed to be overkill, and I don't think it's supported by Active Record as it's a MySQL proprietary function(?). Although I don't have any immediate need to keep my application portable, I try to keep it that way because it's good practice, and you never know when your host is going to change, or when you're going to move to another host.

I think I may have come up with a half-decent solution, however. I am going to make a library, which will create an array for the user to store the data if there isn't data in the database already. In here, I can also code some logic that will be able to tell whether an entry needs to be updated, deleted, inserted or nothing at all. I think creating a library was the answer I was looking for the whole time, as it keeps all the complexities in the library and not dotted through-out my application.

Both of your posts were extremely helpful, so many thanks to you both for taking the time to respond.




Theme © iAndrew 2016 - Forum software by © MyBB