|
|
Posted by Michael Fesser on 10/08/07 16:08
..oO(tartagoo@gmail.com)
>What if you used only one table with more generic colums?
Ugly.
>Let's say we have a table named "entities" (just to mean no matter if
>we have to do with users, clients, accounts etc.).
>The columns could be: id, parent, ref, type, data.
>Ex. of contents:
>1, 0, 'user', '', ''
>2, 1, 'email', 'email', 'username@provider.com'
>3, 1, 'password', 'password', 'akfgKJ8'
>4, 1, 'nick', 'text', 'bobby'
Such a design not only breaks a lot of database design guidelines, it
also makes it very hard to perform even the most basic DB operations.
Joins, grouping, date calculations etc. can become very funny then.
>To retrieve the email of the user who typed in nick and password,
>we'll write something like "select data from entities where parent =
>(select e1.parent from entities as e1 join entities e2 on e1.parent =
>e2.parent where e1.data = '$nick' and and e1.ref = 'nick' and e2.data
>= '$password' and e2.ref = 'password') and ref = 'email'.
I would _always_ avoid such over-generalized approaches if possible.
>That seems a bit complicated but the pro is you don't have to worry
>about witch and how many columns because every attribute become an
>entity child of another entity, where id and parent colums are used as
>internal references and the name of the attribute is the ref.
In some rare cases this might be useful, but usually it's much better to
spend some time thinking about a proper DB design, even if it ends up
with a lot of tables and relations.
>Data should be a longtext type or even a binary one if you're going to
>use this table to store e.g. images as attributes of a certain entity.
>Of course this kind of table could host any kind of things.
>
>While I think this method is easy to handle from the programmer's
>point of view, what I'm tryng to understand is if it is too expensive
>in terms of resources/performances.
It wastes a lot of space and even more performance.
Micha
Navigation:
[Reply to this message]
|