0

I'm designing a SaaS healthcare application to manage activities in multiple organizations. The admin of each organization is allowed to create custom fields for business concepts.

Example: Patient of org X has attribute A and B while Patient of org Y has attribute M and N

After doing research, I find that there're a few considerable design approaches:

Option 1: have a BusinessObjectCustomField for each business table. Example for PatientCustomField

OrgID,   CustomFieldID,   Value
X,       A,               1
X,       B,               2
Y,       M,               3
Y,       N,               4

Option 2: use a single table for each business object, but alter table when new custom field is added. As a result, num(column) ~ num(org) * num(average fields per org)

PatientID    A      B      M      N
1            1      2      NULL   NULL
2            NULL   NULL   3      4

Option 3: use generic purpose columns to hold data. As a result, num(column) ~ max(fields per org)

PatientID    Col1   Col2
1            1      2
2            3      4

Option 4: store custom data as XML

PatientID    CustomField
1            <custom><A>1</A><B>2</B></custom>
2            <custom><M>3</M><N>4</N></custom>

Option 5: create dynamic tables on the fly

Patient_1
PatientID    A    B
1            1    2

and

Patient_2
PatientID    M    N
1            3    4

My design priorities are:

  1. Good performance
  2. Adaptable to change
  3. Easy to code
  4. Space (since data will be a lot)

Which option should I choose? I know that MS SQL Server supports index for XML data, but does it provide equally good performance as relational indexing?

References:

Community
  • 1
  • 1
giangnn
  • 405
  • 1
  • 5
  • 13
  • 1
    What do you mean by "a lot" of data? DO you have any estimate of how many rows? And, what kind of hardware are you going to be running this on? Many design deficiencies can be masked by good hardware. – Stuart Ainsworth Dec 22 '11 at 08:26
  • Also - what kind of usage do you expect? Lots of updates, lots of reads, lots of queries by custom fields? Is rdbms your only persistence option? – Goran Dec 22 '11 at 09:23
  • Some estimates: 500 orgs, each org with about a few thousands of business object, each business object with about 30-40 custom fields (but some can be reused). Also, we can scale up reasonably well with good hardware. Operation wise, update/read is about 1/10 – giangnn Dec 23 '11 at 01:10

0 Answers0