Attribute-value approach for table design

Currently, we have a table, which hold all preferences information of the
company (owner of the application), such as company name, address, phone,
default decimal, tax information. Up to now, this table has one record and 61
columns already. Each time, if the customers want new settings for the
company, we have to add a new field to the table. We also has another table
for application settings as well, which has one record and 44 columns already.

With the new requirement coming, I need to add more settings for the
company. Instead of continuing to add more new fields to the existing table,
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

For example:
AttributeName TextValue DateValue NumberValue BooleanValue
CompanyLogo C:\logo.bmp
CompNameInRpt False
TraceLevel 2

This will give us a flexibility to add any new settings for the company
preferences and for other application settings. I need some advice on this
approach :-).

--
Software Developer

Ming wrote:

Currently, we have a table, which hold all preferences information of the
company (owner of the application), such as company name, address, phone,
default decimal, tax information. Up to now, this table has one record and 61
columns already. Each time, if the customers want new settings for the
company, we have to add a new field to the table. We also has another table
for application settings as well, which has one record and 44 columns already.

With the new requirement coming, I need to add more settings for the
company. Instead of continuing to add more new fields to the existing table,
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

For example:
AttributeName TextValue DateValue NumberValue BooleanValue
CompanyLogo C:\logo.bmp
CompNameInRpt False
TraceLevel 2

This will give us a flexibility to add any new settings for the company
preferences and for other application settings. I need some advice on this
approach :-).

I would use three columns

AttributeName (Text)
AttributeValue (Text)
ValueType (Integer)

e.g.

AttributeName AttributeValue ValueType
CompanyLogo C:\logo.bmp 8
CompNameInRpt False 11
TraceLevel 2 2

where ValueType corresponds to the number returned by the VarType
function, see online help. Then using the ValueType, you can convert
the string in AttributeValue to a properly dimensioned variable.


HTH
Matthias Kläy
--
www.kcc.ch

Hello Matthias Klaey,

Thank you so much for your response. Do you think it is good approach to use
attribute-value type table instead of traditional relationtional table in
this case?


--
Software Developer


"Matthias Klaey" wrote:

Ming wrote:

Currently, we have a table, which hold all preferences information of the
company (owner of the application), such as company name, address, phone,
default decimal, tax information. Up to now, this table has one record and 61
columns already. Each time, if the customers want new settings for the
company, we have to add a new field to the table. We also has another table
for application settings as well, which has one record and 44 columns already.

With the new requirement coming, I need to add more settings for the
company. Instead of continuing to add more new fields to the existing table,
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

For example:
AttributeName TextValue DateValue NumberValue BooleanValue
CompanyLogo C:\logo.bmp
CompNameInRpt False
TraceLevel 2

This will give us a flexibility to add any new settings for the company
preferences and for other application settings. I need some advice on this
approach :-).

I would use three columns

AttributeName (Text)
AttributeValue (Text)
ValueType (Integer)

e.g.

AttributeName AttributeValue ValueType
CompanyLogo C:\logo.bmp 8
CompNameInRpt False 11
TraceLevel 2 2

where ValueType corresponds to the number returned by the VarType
function, see online help. Then using the ValueType, you can convert
the string in AttributeValue to a properly dimensioned variable.


HTH
Matthias Kläy
--
www.kcc.ch

Ming wrote:
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

I don't think you'll find much support for the notorious EAV
(Entity-Attribute-Value) 'design flaw' around here

Jamie.

--

Ming wrote:

Hello Matthias Klaey,

Thank you so much for your response. Do you think it is good approach to use
attribute-value type table instead of traditional relationtional table in
this case?

As Jamie Collins remarked, in general the use of the EAV-model ist
very strongly strongly discouraged.
From a practical point of view, what you deal with here is some kind
of "kitchen junk drawer" table that is present in almost all
applications.
Theoretically, you should put every single parameter value in its own
table, but this is clearly a nightmare to manage. However, you will
need to be extra carefull that the integrity of the information in
this table is properly maintained.

Greetings
Matthias Kläy
--
www.kcc.ch

Hello mattias and Jamie,

thank you so much for your valuable advice. I will do some research to see
what I can do with the existing table and new table, mostly likely I will
take your advice and not to use the EAV-model.



--
Software Developer


"Matthias Klaey" wrote:

Ming wrote:

Hello Matthias Klaey,

Thank you so much for your response. Do you think it is good approach to use
attribute-value type table instead of traditional relationtional table in
this case?

As Jamie Collins remarked, in general the use of the EAV-model ist
very strongly strongly discouraged.
From a practical point of view, what you deal with here is some kind
of "kitchen junk drawer" table that is present in almost all
applications.
Theoretically, you should put every single parameter value in its own
table, but this is clearly a nightmare to manage. However, you will
need to be extra carefull that the integrity of the information in
this table is properly maintained.

Greetings
Matthias Kläy
--
www.kcc.ch

Matthias Klaey wrote:
From a practical point of view, what you deal with here is some kind
of "kitchen junk drawer" table that is present in almost all
applications.

Not in my experience. Applications commonly have 'work' tables that are
loaded/cleared as required but they are strongly typed.

Jamie.

--

Jamie

Can you offer some pointers to arguments both against and for EAV? A
cursory Google search suggested that there are performance issues in this
design, but that for something like a medical tests database, EAV offers a
practical solution.

Any additional evidence, or are we in the realm of opinion?

Thanks!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"Jamie Collins" wrote in message
ps.com...

Ming wrote:
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue
column.

I don't think you'll find much support for the notorious EAV
(Entity-Attribute-Value) 'design flaw' around here

Jamie.

--

Jeff Boyce wrote:
Can you offer some pointers to arguments both against and for EAV? A
cursory Google search suggested that there are performance issues in this
design, but that for something like a medical tests database, EAV offers a
practical solution.

The intent of my post was to give a name to the design, using the word
'flaw' in quotes to reflect the low opinion in general application, to
encourage the OP to do some research. Mission accomplished ;-)

Let's face it: the chances of EAV being a newbie error, rather than
considered design choice, are extremely high.

Bear in mind that 'database' and 'SQL DBMS' are different concepts. My
impression from reading around the subject is that if EAV is the
required solution then SQL is not the best means to achieve those ends
i.e. 'performance issues' are a symptom of a fundamental bad fit.

My feeling is that the limited applications that benefit from EAV would
not be suited to an Access/Jet SQL.

Any additional evidence, or are we in the realm of opinion?

I've no first hand experience to add.

Jamie.

--

Jamie

I understand that some hold EAV in low regard ... I don't find the reasons
why (aside from potential performance). Can you point to some of the
reasons why you consider it a 'flaw'?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jamie Collins" wrote in message
oups.com...

Jeff Boyce wrote:
Can you offer some pointers to arguments both against and for EAV? A
cursory Google search suggested that there are performance issues in this
design, but that for something like a medical tests database, EAV offers
a
practical solution.

The intent of my post was to give a name to the design, using the word
'flaw' in quotes to reflect the low opinion in general application, to
encourage the OP to do some research. Mission accomplished ;-)

Let's face it: the chances of EAV being a newbie error, rather than
considered design choice, are extremely high.

Bear in mind that 'database' and 'SQL DBMS' are different concepts. My
impression from reading around the subject is that if EAV is the
required solution then SQL is not the best means to achieve those ends
i.e. 'performance issues' are a symptom of a fundamental bad fit.

My feeling is that the limited applications that benefit from EAV would
not be suited to an Access/Jet SQL.

Any additional evidence, or are we in the realm of opinion?

I've no first hand experience to add.

Jamie.

--