onion2k wrote:It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.
This is what you said in your first post: you think that normalized data which is currently stored as 200 separate rows should be denormalized to a 200 column schema instead, in the name of performance. The reason you give is "fetching and updating all the data at the same time". This can be easily verified as bad advice, plan and simple. In the world of IT, this is called "premature optimization" and it is the "root of all evil" for a very good reason.
In your last post you are still sticking by your "turn the 200 records into 200 columns advice" mostly all throughout, then at the last minute you offer this:
onion2k wrote:
It's only better to store them as rows if the structure is flexible, eg people can add or change what each item is meant to represent. As it happens that's the case here but we only discovered that after my recommendation.
So are you saying that all of a sudden you changed your mind and what you offered as good advice earlier has turned into bad advice now? What made you change your mind?
onion2k wrote:but we only discovered that after my recommendation
I think by "we" you must mean "you", because to anyone who read the original post with any level of attention it became obvious that his schema was already normalized. Certainly that must be the case for his page to require 8200 inserts and for you to propose reducing those inserts by way of sacrificing his (currently normalized) schema. So at some level you must have realized what was the original state of affairs before you proposed that change.
Pay special attention to where the OP mentions "the ID of the skill": that means there is a "skills" table, which means there probably is also a "skill_ratings" table, which is the one that gets 8200 inserts as of right now.
That one is the table you are proposing to add 200 columns to (just so you understand the implications of your advice), that's the one that would have one new column for every skill in
your proposed solution. Ask anyone with any serious experience with database design and they will tell you that's called "denormalizing a schema".
It sounds to me you are beginning to see the problems with your proposal, but you cant quite find a way to say "I think I maybe have been wrong". Fine by me, but at least make sure that the OP understands that your original advice was not sound advice, for his own sake.
You don't need to make up contrived examples about users and passwords since we have a perfectly good example to refer to: the original post. That was the context of your advice.
Stryks wrote:I'd really have to disagree with this simplification...
I was counting on your intelligence to realize that this simplification is only valid in the context of the original problem, but it seems to me you haven't taken the time to read or understand said original problem, given that you make no mention of it in your post. You seem pretty confident with your "of course" statements and calling your opinion a "fact already acknowledged", so confident indeed that you should have no trouble explaining to everyone reading this thread why
in the context of the original problem and schema it is "better" to turn what currently is stored as rows into more columns on the same table.
Will you
please explain that for the benefit of both the OP, myself, and anyone else out there who hasn't yet seen why this could ever be a good idea? They say if you can't explain something clearly and in simple terms it means you don't really understand it. Let your clarity shine through.
Stryks wrote:Let's get back on thread and try and be productive instead of argumentative.
I agree. Start with yourself by reading the original post and proposing some solution to it, rather than stating "facts already acknolwedged".