For anyone who knows a lot about mysql. Lets say you had like 50 user preferences (most of which aren't just true/false), would it be best to have seperate field for each one or combine them all into a single field somehow? It seems to me that having a ton of fields could possibly make a table too slow/big or something. Basically, does anybody know the best way to store a bunch of preferences?
Anyway, I'm gonna bore you guys with another blog about my website work, mostly because typing it out in a blog helps me think.So I started work on the global private message thing, I've created the database for the messages and even have the forum system checking it, though it doesn't do anything with it yet.To simplify things, I also created 2 other databases for site identification. One of them is a domain database, which just gives me an id number for each domain I own. The other is a site database, which gives every site its own id, name, folder name, and the domain id. That should make it a lot easier for me to do cross site stuff.Anyway, I still haven't decided whether or not I should keep the local private messages. I need to keep the database anyway, because its used for the shoutboxes, but keeping it means twice the queries just to check your private messages and I don't know if it provides much of a benefit.I also came up with a new system for storing ip addresses. Right now, I just store the actual string for every post and topic, it works but I think I could do it better. My idea is basically to have this huge database of ip addresses that have visited my site, all it lists is a number id, and the ip. I'll start out just listing the ip as a normal string, but eventually I could make it smaller by processing the ip. Then I would just have to save a single number to reference the ip, which would probably shrink my total database size by a ton, especially once ipv6 (2001:838:2:1:2a0:24ff:feab:3b53 wheeeee) becomes standard.One of my favorite parts of this is that you could let mods handle ip address stuff without ever seeing the actual ip.Well thats it for now!
If they're all boolean values, you could save the values bitwise - 1 byte = 8 preferences.
Well like I said, most of them wouldn't be boolean. Mostly I'm wondering if theres a significant enough side effect to having a ton of fields in a table, even if the data inside is just something like a smallint or char.
I don't think having each preference as a separate field with an entry for each user id would be too detrimental to performance. That's how phpbb does it I think.
What about ipv6? ip2long seems to be ipv4 only.
As for the mysql, it would never need to be sorted by these values but some of them would have to be loaded by various people fairly often. For example, whether or not the email address can be shown. Or their birthday.ip2long is for ipv4, but there are a few fixes for that posted on that doc page and it shouldn't be too hard to write one yourself using sscanf. Either way, storing IP's as numbers is what was originally intended.
Having a lot of columns shouldn't cause any problems unless you do something really stupid like SELECT * all the time.
Excuse me, I believe this has something to do with the Pyco thing which was mentioned in your previous blogs. But what *exactly* is this Pyco thing?
Pyco is my network of websites which all use the same account system and will eventually let people create forums and blog communites and stuff.