I am along the way of repairing a credit card applicatoin (lone developer here) using PHP and PostgreSQL. For the majority of the data, I am storing it utilizing a table with multiple posts for every attribute. However, I am now beginning to construct a few of the tables for that content storage. This content within this situation, is multiple sections that every contain different data sets a few of the information is common and shared (and foreign key'd) along with other information is very unique. In the present iteration from the application there exists a table structure such as this:

id | project_name | project_owner | site | customer_name | last_updated
1  | test1        | some guy      | 12   | some company  | 1/2/2012
2  | test2        | another guy   | 04   | another co    | 2/22/2012

Now, this works - however it will get difficult to maintain for any couple of reasons. Adding new posts (happens rarely) requires modifying the database table. Audit/history monitoring takes a separate table that mirrors the primary table with a lot more information - that also requires modification when the primary table is transformed. Finally, you will find lots of posts - over 100 in certain tables.

I have been brainstorming alternative approaches, including breaking out one large table into numerous more compact tables. That introduces other conditions which i feel also create problems.

The approach I'm presently thinking about appears to become known as the EAV model. I've got a table that appears such as this:

id | project_name | col_name | data_varchar      | data_int | data_timestamp | update_time
1  | test1        | site     |                   | 12       |                | 1/2/2012
2  | test1        | customer_name | some company |          |                | 1/2/2012
3  | test1        | project_owner | some guy     |          |                | 1/2/2012

...and so forth. It has the benefit that I am never upgrading, always placing. Information is never over-written, only added. Obviously, the table will ultimately come to be big. I've an 'index' table that lists the projects and it is accustomed to reference the 'data' table. However feel I'm missing something large with this particular approach. Does it scale? I initially desired to perform a simple key -> value type table, but recognized I have to have the ability to have different data types inside the table. This appears managable since the database abstraction layer I am using includes a kind that chooses data in the proper column.

Shall We Be Held making an excessive amount of work with myself? Must I stick to an easy table with a lot of posts?

Make an effort to that if you're able to stay away from an EAV table, achieve this. They are usually performance murders. They're also hard to correctly query specifically for confirming (Yes allow me to join for this table a mystery number occasions to obtain all the data from it I want and, oh incidentally, I'm not sure what posts I've available and so i have no clue what posts the report will have to contain) which is challenging the type of database constraints you need to ensure data integrity (how to make sure that the needed fields are completed for example) also it can lead you to use bad datatypes. It is much better over time to define tables that keep data you'll need.

If you're actually need the functionality, then a minimum of consider NoSQL databases for enhanced with this kind of undefined data.