Page 4 of 4
Re: New PHP Framework
Posted: Mon Sep 15, 2008 6:46 pm
by Christopher
allspiritseve wrote:I don't think you understand me. Foreign keys would be explicit associations.
Yes and no. EAV is essentially a convention. If you followed say the Rails style key naming convention then you could always infer the relation's table and key name. Not 100% the same, but similar result. Each has its pros and cons.
Re: New PHP Framework
Posted: Wed Sep 17, 2008 10:50 pm
by allspiritseve
arborint wrote:allspiritseve wrote:I don't think you understand me. Foreign keys would be explicit associations.
Yes and no. EAV is essentially a convention. If you followed say the Rails style key naming convention then you could always infer the relation's table and key name. Not 100% the same, but similar result. Each has its pros and cons.
I still don't think you understand me.
What I was trying to do was take the main thing I liked about EAV-- associations-- and apply it to a standard database. In a standard database, a foreign key references an id from another table. It can't reference multiple tables, because you could potentially have ID clashes. What I was proposing was a separate table that sort of audited IDs. Instead of an id that auto-incremented table-wide, you could have an id that auto-incremented database-wide. Adding a row in table1 adds id 201 in global table. Adding a row in table2 adds id 202 in global table. Adding a row in table3 adds id 203 in global table. etc...
This means a foreign key, if it referenced an id in the global table, could reference a specific row from any of a number of tables, as long as those tables have an associated id in the global table. This is what I meant by a "general association"-- a foreign key that wasn't aware of what table it referenced, since it references the global table id and not the specific table id. A standard foreign key has to know what table it references, otherwise it isn't much use.
Re: New PHP Framework
Posted: Wed Sep 17, 2008 11:31 pm
by Christopher
The keys could be autoincrement columns or sequences (which sounds like what you are describing). It does not really matter if you have everything in one table or have a 'global table' or just use a sequence when you create records. Something still needs to relate the records together. Something has to create all of those records with the same key value -- either all at once or starting somewhere. If something is keeping track of that then I don't see why you need the 'global table'. If you put everything in one table then you end up doing evaling and serializing. And none of this has much to do with meta data because you can have that with any structure really.
Re: New PHP Framework
Posted: Wed Sep 17, 2008 11:42 pm
by allspiritseve
I don't really understand where eval, serialization, and/or meta data come into this. A simple join would do the trick.
I'm not really sure what sequences are, but if you have an example of how they would take the place of a global id table, I'd like to see it.
Re: New PHP Framework
Posted: Thu Sep 18, 2008 12:43 am
by Christopher
allspiritseve wrote:I don't really understand where eval, serialization, and/or meta data come into this. A simple join would do the trick.
Sorry, I was commenting as part of this larger discussion of EAV style data tables and this proposed CMS database design.
allspiritseve wrote:I'm not really sure what sequences are, but if you have an example of how they would take the place of a global id table, I'd like to see it.
Sequences are what DB2, Oracle, Postgres (I think they are called Identities in MSSQL) have in addition to autoincrement column. They are named counters that can be used by multiple tables. You can emulate them with MySQL.
I think my point was that some logic needs to create records with the unique id. So either they are all created at once, or you build records off of a key in an existing record. As long as the keys are unique (which sequences provide), you don't need the global table because there is no reason to go through it when you can go directly to another table.
Re: New PHP Framework
Posted: Thu Sep 18, 2008 1:16 am
by powerofq
These recent posts only emphasize the need for simplicity. I haven't dealt with keys, tables or joins at all, and written almost ZERO SQL for years. This DB is basically a black box. It's fast, and it's self-managing.
Let's say a client comes to me and wants to start keeping track of student grades. I don't need to think of tables or the like.. I give the client a simple web page form with 3 fields, a single PHP function, and start inserting declarations. This data could be mixed in anywhere, as long as the first rule is followed -> ~All subjects must be unique~
Johnny42 isa student
Johnny42 fname Johnny
Johnny42 lname Smith
Johnny42 submittedtest a4903
a4903 isa englishtest10
a4903 marked? True
a4903 score 75%
a4903 date 080611
englishtest10 totalnumberofquestions 112
englishtest10 createdby Bob
Bob phonenumber 555-555-1212
And I'm done.. in less time than it took me to type that out.