05-11-09, 16:20 #1Registered User
- Join Date
- May 2009
Efficient for handingly dynamic fields?
This is my first time posting. I really want to learn more about efficient database design. I have an idea I'd like to run by you.
I would like my database to have store data in a dynamic way. A typical case data DB looked like this:
CaseID | Case# | FirstName | LastName
1 | 0045F | Brad | Smith
If you want to add, say, "MiddleName" field you'd need to add a new column to the DB which caused trouble for our code in the past. I have an idea to do it dynamically like this:
FieldID | FieldName
1 | FirstName
2 | LastName
3 | MiddleName
CaseID | Case#
1 | 0045F
CaseDataID | CaseID | FieldID | Data
1 | 1 | 1 | Brad
1 | 1 | 2 | Smith
1 | 1 | 3 | Lee
The CaseData table would be incredibly long. I realize some provisions would need to be made for multiple data types. However, structurally, is this efficient? In other words, is it easier for a DB to read a FAT table with a ton of data but less rows, or a LONG table with a ton of data but more rows?
Using this design we could VERY easily add new fields by adding to the Field Table. and anyone who has data for the newly added field would just get a new record in the CaseData table.
Tell me what you think. Thanks!
05-11-09, 16:50 #2Registered User
- Join Date
- Dec 2007
- London, UK
You don't need to do any such thing. The fact is that EVERY database design is dynamic.. or should be assumed to be so. The best way to deal with change is to have lean change control processes that are able to respond efficiently to changing business requirements. That means developers, DBAs, etc. It's about people and process, not technology.
The wrong way to deal with change is to dismantle and obfuscate your data model (your "EAV" anti-pattern) on the grounds that your developers don't have time to maintain it. That's like admitting that your developers / database support team aren't upto the job.
Last edited by dportas; 05-11-09 at 16:54.
05-11-09, 18:07 #3Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
As DPortas pointed out, in at least 999 of 1000 cases EAV is a very tempting disaster in search of its next victim. There are a few places where VERY SMALL, unmanaged data stores need the flexibility of EAV such as when your application needs to replicate the hive structure of the Windows registry.
I've never seen a case where a structured store (something that might be reported as rows and columns) ever needed to be stored using EAV, or anywhere that EAV worked nearly as well as normal tables for structured data.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.