Agreed with brobb355.

Also, I don't think MySQL is the best example of a relational database. From what I've heard it's quite infamous for its lack of true database features and deviations from SQL standards.

I'll try to explain my side a little more clearly.

Kyareshi, there is good reason why I say no query should ever fail. In a word: debugging. In a lot of words: If I have a key constraint, and notice a script fails to execute as I coded it to, I have to debug it. If I isolate the problem to be a bad query, I can run it manually, and read the unhelpful error message. I don't know about the SQL server you are using, but MySQL gives useless error messages. On top of that, if in a set of 1000 assignments, one failed the key constraint, all 1000 would fail - not to mention, no hint of where that one was. Bad data might be difficult for you to remove, but for me it's typically done after a couple of manual queries. Hardly a big deal. So, by having bad data, I can learn more about the problem, isolate it, and write a function to insure that bad data never gets inserted again.

Plain and simple, I don't trust keys. And I don't use software code I don't have faith in. You're not going to change that by any argument. Whatever point or purpose any kind of key could possibly serve, I can implement myself by writing a routine in PHP.

brobb355, you greatly underestimate my understanding of databases. I know enough already to write my own version of MySQLD in C optimized for Anime Characters Database. I don't consider there to be anything meaningful left for me to learn. I know what an ultra fast database server should do, and I know how to code it.

Whenever I use terminology loosely, it's because I already know far more about the topic than you could ever imagine.






I don't get the feeling either of you understood the genius behind my tag database.

If you had understood, you'd have suggested I use the built-in string hash function rather than an INT text based lookup. Anyways, let's continue.

The table structure is this:
tags: id(INT), tag(TEXT)
tagged: id(INT), corp(TINYINT), tag(INT)

Say I want to tag character 100 "katana".
We'd end up with two INSERT's:
tags: 9, "katana"
tagged: 100, 1, 9

Then to tag character 222 "katana".
We'd have just one insert.
tagged: 222, 1, 9

I'll have to assume neither of you know what I am doing with these tags.
Tag based searches require very fast tag lookups. Kyareshi, you suggested having a TEXT tag field on the character tables. Assuming all 18,000 have comma separated tag fields, that means to find characters tagged katana, SQL would need to search though all 18,000 characters, from beginning to end of variable length tag fields. Even properly indexed, any tag longer than 2 characters would require matching more bytes than than my INT table column. With indexes, you would cut down the number of potential matches, but it's still not possible to beat an INT for the amount of data per field to check.

For my uses, I need speed searching. INT's are untouchable for speed. Every word and phrase gets assigned a number, and that number is used in place of the word for tagging. Thus, knowing the ID for the tag word, searches are done quick, and can then be equally as quick translated back into their original words.

私は日本語を少し読むと書くをできます。あなたも？

Rei Kyareshi, there is good reason why I say no query should ever fail. In a word: debugging. In a lot of words: If I have a key constraint, and notice a script fails to execute as I coded it to, I have to debug it. If I isolate the problem to be a bad query, I can run it manually, and read the unhelpful error message.

Any database that's worth its salt would give you at least the name of the constraint that failed (as well as on which table). With this I can determine the cause of the problem in mere seconds. If the query goes through, however, you might not notice the problem until much later when you look at the data. If I let this happen at one of our clients, the results could be disasterous.

Rei I don't know about the SQL server you are using, but MySQL gives useless error messages. On top of that, if in a set of 1000 assignments, one failed the key constraint, all 1000 would fail - not to mention, no hint of where that one was.

And this is why I hate MySQL.

Rei Bad data might be difficult for you to remove, but for me it's typically done after a couple of manual queries. Hardly a big deal.

Don't make assumptions. Removing bad data is a cinch. But you have to know about it in the first place. If queries allow faulty data to slip into your database, you might not discover it for months.

Rei So, by having bad data, I can learn more about the problem, isolate it, and write a function to insure that bad data never gets inserted again.

So basically, I take it you're using bad data to guide you because of a flaw in your database engine's treatment of key constraints? This is starting to make sense.

Rei Plain and simple, I don't trust keys.

This is a nonsensical statement. You're using keys all over the place. You probably mean constraints, in which case I feel it's necessary to point out that it is in fact MySQL's error messages that are unreliable (at least according to your feedback), not the constraints themselves.

Rei You're not going to change that by any argument.

The moral of this thread.

Rei Whatever point or purpose any kind of key could possibly serve, I can implement myself by writing a routine in PHP.

You're missing the point again. As I've stated before in this very thread, the purpose of key constraints is not to replace your routines but to supplement them as a database side failsafe.

Rei Basics? What basics? You're forgetting I'm a CS drop out. :-P

Rei brobb355, you greatly underestimate my understanding of databases. I know enough already to write my own version of MySQLD in C optimized for Anime Characters Database. I don't consider there to be anything meaningful left for me to learn. I know what an ultra fast database server should do, and I know how to code it.

So which is it, Rei? :o

Rei Whenever I use terminology loosely, it's because I already know far more about the topic than you could ever imagine.

"If I sound dumb, it's because I'm actually a lot smarter than you."

Rei I don't get the feeling either of you understood the genius behind my tag database. If you had understood, you'd have suggested I use the built-in string hash function rather than an INT text based lookup.

Judging by the table definitions you listed just now, I understood your tag table structure just fine. I don't see what you're getting at with the string hash function, though, but it you seem to be contradicting yourself. If we'd understood your genius we would have suggested you do something else?

Rei I'll have to assume neither of you know what I am doing with these tags.

Again, I understood perfectly, as verified by the table structure you just posted. You're using the "tagged" table as an intermediate table between the "tags" table and whatever else you are tagging. It's a solid design, if nothing to write home about.

Rei Kyareshi, you suggested having a TEXT tag field on the character tables. Assuming all 18,000 have comma separated tag fields, that means to find characters tagged katana, SQL would need to search though all 18,000 characters, from beginning to end of variable length tag fields.

I'm positive I never suggested this. Link to post or it didn't happen.

Any database that's worth its salt would give you at least the name of the constraint that failed (as well as on which table). With this I can determine the cause of the problem in mere seconds. If the query goes through, however, you might not notice the problem until much later when you look at the data. If I let this happen at one of our clients, the results could be disasterous.
-I still don't care about the bad data. You have clients, so now I understand why it plays an important role in what you do. If our situations were the same, I'd agree. However, I don't have clients.

And this is why I hate MySQL.
-I think we can both agree on a shared dislike of MySQL. Out of curiosity, what framework do you use?

Don't make assumptions. Removing bad data is a cinch. But you have to know about it in the first place. If queries allow faulty data to slip into your database, you might not discover it for months.
-By my making assumptions, you cleared up perfectly a lot of my misunderstandings. Again, bad data isn't a problem until I know about it. And when I do know about it, I don't have to worry about motivating myself to work that day. ;-) Crisis = ultimate motivator.

So basically, I take it you're using bad data to guide you because of a flaw in your database engine's treatment of key constraints? This is starting to make sense.
-Yes.

This is a nonsensical statement. You're using keys all over the place. You probably mean constraints, in which case I feel it's necessary to point out that it is in fact MySQL's error messages that are unreliable (at least according to your feedback), not the constraints themselves.
-So you call them constraints? So be it. I still don't trust them. Yes, I know from a technical and logical standpoint your side. But there's that moral of the thread. Heh.

So which is it, Rei? :o
-"If I sound dumb, it's because I'm actually a lot smarter than you." It's that one. Still making up my mind on the better approach. Should I be modest, and have people patronize me, or should I be conceited? Either way, behind the approach the facts are the same - I have ultimate confidence in all I do.

Judging by the table definitions you listed just now, I understood your tag table structure just fine. I don't see what you're getting at with the string hash function, though, but it you seem to be contradicting yourself. If we'd understood your genius we would have suggested you do something else?
-Ahhh forget about it... You're sane, so I'd just be wasting my time explaining an insane approach even better than what I used. I remembered something about Java, where every string is assigned a unique memory address based on a hash of the string contents. Duplicate strings end up sharing the same address, or something of the sort. Kinda like printing a string in C, but passing the register value instead of a pointer to the data. You end up with a 4 byte INT. Assuming PHP uses a similar method of tracking strings in RAM address space, and further assuming that each unique string identifier remains constant at each run time... it's worth assuming you could use that as a no cost hash value. Presently, I have to look up the ID in a database based on the word... however, if the language environment has already assigned a register value / 4 byte INT as an identifier for that string, I could use that without any need of the tags table.

As for the tables, I think that's all clear. What I remembered was different from what you wrote.

私は日本語を少し読むと書くをできます。あなたも？

I'm sure you have a lot of prowess in this type of thing but if you intend on getting help from others some compromise is going to be needed as well as a mutual understanding of the concept material. That is where most of the confusion and clashing is being had here, a lack of mutual understanding of the subject matter on account of different means of learning the needed concepts. You focus on speed here, Kyareshi and I are focusing on simplicity and normalization. You have a very complex database going here for the sake of speed.

Speed, limitless storage, no duplication, and future multilingual support, is what I look for in all my table designs.

I thank you both for the offers of help. It's just, I'm quite happy with my way of coding.

私は日本語を少し読むと書くをできます。あなたも？

I bit of debate is good for the mind. It forces you to think about things from a different perspective, and you learn a great many things doing that.

Rei I think we can both agree on a shared dislike of MySQL. Out of curiosity, what framework do you use?

I rather like Firebird.

Rei So you call them constraints? So be it. I still don't trust them.

Yes, that is what they are called. The term "key" refers to a value used to identify something. The "constraint" is the check that verifies the data. Regardless, I don't think it's a matter of trust. If my database engine gave me such terrible error messages, I would probably be reluctant to employ the constraints myself (at least until the last minute), for the sake of convenience.

Rei Still making up my mind on the better approach. Should I be modest, and have people patronize me, or should I be conceited? Either way, behind the approach the facts are the same - I have ultimate confidence in all I do.

You may not care, but personally I think a laidback approach suits you best. That way you don't come across as a jerk, and on the flip side you can play the "it doesn't really matter, I'll do what I want" card whenever you want.

Laidback would be staying true to myself, alright.

Programming is one of those areas my ego tends to get a bit inflated. Which privately I like to use to my advantage. If you think you are average, you get average results. If you think you are godlike, you just might pull off the occasional better than average result. lol None the less, history and experience tells me, there's much left I'll learn.

On an aside, Two years ago, I had no comprehension of how to do complex register memory moves. I now know this program will return 5 as its exit code, and I finally know how to get at any value in the numbers list.

.data
numbers:
.long 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
.bss
.text
.globl _start
_start:
# Intel = SECTION:[BASE + INDEX*SCALE + DISP]
# AT&T = SECTION:DISP(BASE, INDEX, SCALE)
# DISP: int (optional)
# BASE: register (optional)
# INDEX: register (optional)
# SCALE: int [1, 2, 4, 8] (optional, default = 1)
# SECTION: section register (optional, see a 80386 manual for defaults)
xor %eax, %eax # fill eax with 0
incl %eax # add 1 to eax, value = 1
incl %eax # add 1 to eax, value = 2
incl %eax # add 1 to eax, value = 3
movl $numbers, %ecx # move address of numbers into ecx
movl 4(%ecx,%eax,4), %ebx # complex address access
decl %eax # decrease eax by 1, value = 2
decl %eax # decrease eax by 1, value = 1
int $0x80 # call Linux exit() function with status code stored in ebx

Translation, we start out at BASE %ecx, which is have the pointer to $numbers, which contains the value "1"
We use a DISP of 4 to add 4 bytes, thus moving up one number, to 2.
We then use INDEX %eax which has the value 3, multiplied by 4 SCALE ensuring we move ahead 3 words.
Taking us from 2, to 5!

Save as mem.s
On Linux,
as mem.s -o mem.o; ld mem.o -o mem; strace ./mem
or
as mem.s -o mem.o; ld mem.o -o mem; ./mem; echo $?


Muhahahahaha

[ Edited Jun 30, 2009 ]

私は日本語を少し読むと書くをできます。あなたも？

And so, Rei acts on his need to post some totally unrelated code in order to repair his damaged ego.

Whoops, gomen! hehe, but I needed that so I'd have the confidence to refactor some old code.

Fixing up viewone.php and publisher.php has been taking a lot out of me lately. I hope you guys can bear with my mood, hmm, well fluctuations. ほんとに、ごめん。

私は日本語を少し読むと書くをできます。あなたも？