A common database approach
The application I am running into on a client looks like this:
- it allows end users to enter "materials".
- They can add any number of "properties" to these materials.
- Properties can be of any type: decimal, int, dateTime and varchar (length varies from 5 characters to large chunks of text),
Basically, the diagram looks like this:
Materials
MaterialID int not null PK
MaterialName varchar (100) not null
Properties
PropertyID
PropertyName a varchar (100)
MaterialsProperties
MaterialID
PropertyID
PropertyValue varchar (3000)
An important feature of the app is the search function: end users can search for content by entering queries such as:
- [property] checkDate> [DateTimeValue]
- [property] serialNr = 35465488
Guess how this is done on the MaterialsProperties table with nearly 2 million entries.
The database was originally created for SQL Server 2000 and then ported to SQL Server 2005
How can this be done better?
source to share
- Since users can enter their own property names, I assume that each query will include a property table validation (in your example, I need to find propertyid [inspectDate]). If the property sheet is large, your connection will also take a long time. You can try and optimize by denormalizing and preserving the name with propertyID. This will be the denaormalized column in the MaterialsProperties table.
- You can try adding the property type (int, char, etc.) to the materialproperty table and split the table into type.
- Have a look at the Object / Entity Relational Mapping methods to optimize queries.
- Since you already have a lot of data (2 million records), do some data to see if there are duplicate property groups for many materials. You can put them in one schema and the rest in the EAV table. Check out the details here: http://portal.acm.org/citation.cfm?id=509015&dl=GUIDE&coll=GUIDE&CFID=49465839&CFTOKEN=33971901
source to share
You might consider splitting the MaterialProperties table into a typel, for example. in IntMaterialProperties
, CharMaterialProperties
etc. It will be:
- Divide your data.
- Allow potentially faster searches for integer (or other numeric) types.
- Potentially reduce storage costs.
You can also enter a column Type
in Properties
which you can use to define the table MaterialProperties
for the query. The column can also be used to validate user input, eliminating the need to query for "bad" input data.
source to share