On 8/12/2014 6:19 AM, Jimmy Thrasibule wrote:
I'm looking into gathering information about geographical locations:
- Coordinates
- Place name
- Pictures
- etc.
Since a place can be anything, a specific place may need any kind of
data type to be described.
I'm therefore looking into using the same method as the semantic Web
and trying to describe a place with triples.
1. The "triples" table will reference a subject, attribute and value.
2. The "attributes" table will have an attribute name, a type and
mauve a table name.
3. For each attribute type, a new table is created to store the values.
4. A "resources" table list all the available resources (location,
picture, ...).
Using this, I can easily add a new resource and describe it by adding
new triples. Every item is identified using UUIDs as primary key.
that is a variation of an anti-pattern known as EAV
(Entity-Attribute-Value).
its impossible to implement well-performing queries with this as you'll
need to make several sequential queries each time, since table names
can't be 'dynamic' in a sql query by design. (query triples join
attributes, case on the attribute type, query appropriate attribute
table and possibly resources)
http://mikesmithers.wordpress.com/2013/12/22/the-anti-pattern-eavil-database-design/
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
