Database design rules to follow for a programmer -

we working on mapping application uses google maps api display points on map. points fetched mysql database (holding 5m + records). entities stored in separate tables attributes representing individual properties.

this presents following problems:

1. every time there's new property have make changes in database, application code , front-end. fine properties have added entities that's when becomes nightmare go through 50+ different tables , add new properties.

2. there's no way find entities share given property e.g. no way find schools/colleges or universities have geography dept (without querying schools,uni's , colleges separately).

3. removing property equally painful.

4. no standards defining properties in individual tables. same property can exist different name or data type in table.

5. no way link or group points based on properties (somehow related point 2).

we thinking redesign whole database without dba's , lack of professional db design experience struggling.

another problem we're facing new design there lot of shared attributes/properties between entities.

for example:

an entity called "university" has 100+ attributes. other entities (e.g. hospitals,banks,etc) share quite few attributes universities example atm machines, parking, cafeteria etc etc.

we dont want have properties in separate table [and linking them entities w/ foreign keys] require adding/removing manually. generalizing properties results in groups containing 50+ attributes. not records (i.e. entities) require properties.

so keeping in mind here's thinking new design:

• have separate tables each entity containing basic info e.g. id,name,etc etc.

• have 2 tables attribute type , attribute store properties information.

• link each entity (or table if like) attribute using many-to-many relation.

• store addresses in different table called addresses link entities via foreign keys.

we think allow more flexible when adding, removing or querying on attributes.

this design, however, result in increased number of joins when fetching data e.g.to display "attributes" given university might have query 20+ joins fetch related attributes in single row.

we desperately need know opinions or possible flaws in design approach.

thanks time.

in trying generalize question without more specific examples, it's hard critique approach. if you'd more in depth analysis, try whipping er diagram.

if data model changing you're adding/removing properties , many of these properties overlap, might better off using eav.

otherwise, if want maintain relational approach finding lot of overlap properties, can analyze entities , abstractions link them.

ex) db has puppies, kittens, , walruses hasfur , furcolor attribute. remove attributes 3 tables , create furryanimal table links each of 3.

of course, simplest answer not touch data model. instead, create views on underlying tables can use address (5), (4) , (2)