How far can I take this database design?

I am interested in knowing the pros and cons of creating a custom system supported by a database like the one described below:

It ha开发者_运维百科s 6 tables that support it.

Entity: Lets say, anything "physical" that can exist and have detail stored against it (Hilton Hotel, Tony Taxi, One Bar)

Entity Type: A grouping/type of entity (Bar, Hotel, Restaurant)

Metadata: Any detail describing or belonging to an entity item (IR232PH, foo@bar.com, 555-555-555)

Metadata Type: A grouping/type of metadata (Post Code, Telephone, Email, address)

Entity Relationship: The ability to group any entity item to another (Entity1-Entity2, Entity3)

Entity Relationship Type: The grouping/type of entity relationship.

I can see how this model is good for Entities that are similar but don't always have the same amount of attributes.

What are the pro/cons of using it as it is for entities as described?

• An artist can be performing (relationship type) at a venue.
• An artist can be supporting (relationship type) another artist

What would be the pro/cons of using it also to store more standard entities like users of the system?

• A user can have a favourite (relationship type) venue/artist/bar etc
• A user can have a attending (relationship type) event

Would you take it as far as having the news and blog posts in it?

This is highly subjective, but before I went up the abstraction ladder to where you are suggesting, I'd rather code my application to use DDL to modify the database schema to match the concrete aspects of the actual entities it was using, rather than having a static schema abstracted so far as to be able to store data about any potential entities.

In a way, to be a bit facetious, IMHO, what you are suggesting has already been done.... It is called a Relational Database. Every RDBMS is a software tool designed to be able to model any possible set of entities, and their attributes, in a way that accurately models those entities and the relationships between them.

Although you can certainly store the data in such a data model, there are a couple of problems (at least) with it.

The first problem is controlling the data. When an 'hotel' is described, what is the set of attributes and metadata that must be defined? Which metadata types can legitimately be entered for an hotel? Related to that is 'when I delete an hotel from the list, what else do I have to delete'? When I delete all hotels from the list (and I never want to store information about hotels again), what else do I have to delete? It is terrifically (terrifyingly?) easy to get all sorts of stray extraneous, unreferenced data into the database.

The second problem is retrieving the data. Suppose I want to know all the information about a specific hotel? How do I write a query for that? Actually, even inserting the data is hard, but selecting it is, if anything, harder. If I only want three attributes, it is easy - if the hotel actually has them all. It is harder if the hotel only has two of the three specified. But suppose the hotel has 30 atttributes, which is not a lot. Then it is terrifically difficult.

What you are describing is a souped-up version of a model known as the EAV or Entity-Attribute-Value model of data. It is generally accepted to be a 'bad idea', for all it is a common idea.

What you described is also known as a triplestore. A triple is a subject-object-predicate (Hotel HAS Rooms, Joe Likes HotelX, etc.). There are mechanisms for running these things (triplestore implementations), controlling the data (eg with ontologies) and for querying them, too (eg the SPARQL language). However, this is all fairly bleeding edge stuff and is known to have scalability problems. Nevertheless, combined with NoSQL approaches (index all your hotels in a big document store, etc.), it's an interesting area to keep an eye on.

See: http://en.wikipedia.org/wiki/Triplestore.