If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Attribute-value approach for table design
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 |
#3
|
|||
|
|||
Attribute-value approach for table design
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 |
#4
|
|||
|
|||
Attribute-value approach for table design
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. -- |
#5
|
|||
|
|||
Attribute-value approach for table design
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 |
#6
|
|||
|
|||
Attribute-value approach for table design
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 |
#7
|
|||
|
|||
Attribute-value approach for table design
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. -- |
#8
|
|||
|
|||
Attribute-value approach for table design
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. -- |
#9
|
|||
|
|||
Attribute-value approach for table design
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. -- |
#10
|
|||
|
|||
Attribute-value approach for table design
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. -- |
Thread Tools | |
Display Modes | |
|
|