> I would strongly suggest that you use a proper relational schema,
> instead of storing everything in two tables. I don't know your
> application, but a schema like that is called an Entity-Attribute-Value
> (though your entity seems to be just posx and posy) and it should raise
> a big red flag in the mind of any database designer. In particular,
> constructing queries against an EAV schema is a major pain in the ass.
> This has been discussed before on postgresql lists as well, you might
> want to search and read the previous discussions.
I get your point, but the thing is the attributes have no particular
relation to each other, other than belonging to same attribute groups.
There are no specific rules that states that certain attributes are always
used together, such as with an address record. It depends on what
attributes the operator wants to study. This is why I don't find any
reason to group the attributes into separate tables and columns.
I am still looking into the design of the tables, but I need to get at
proper test harness running before I can start ruling things out. And a
part of that, is for example, efficient ways of transferring the insert
data from the client to the db, instead of just single command inserts.
This is where bulk transfer by arrays probably would be preferable.
> Ignoring the EAV issue for a moment, it's hard to give advice without
> knowing what kind of queries are going to executed. Are the lookups
> always going to be by id? By posx/posy perhaps? By attribute?
the query will be by attribute type and posx/y. So for position x,y, give
me the following attributes...
thomas