studio - no puedo modificar tablas en sql server 2008

¿El mejor diseño de mesa para la configuración de la aplicación o la configuración de la opción de la aplicación?

Question

Necesito almacenar una serie de valores de configuración en una base de datos. Un par de maneras en que pensé guardarlas son: ¿una tabla con 2 columnas (nombre, valor) y una fila para cada par, o una tabla con una columna para cada parámetro de configuración y 1 fila? Con el primero solo necesito agregar otra fila para agregar un valor de configuración, en el segundo necesito agregar una columna a la tabla. ¿Hay algún problema con cualquiera de los dos debería tener en cuenta? ¿Es uno más eficiente que el otro?

Answer 1

"Lo mejor" depende completamente del contexto: ¿cómo se usarán estos datos?

Si todo lo que necesita hacer es almacenar y recuperar un solo conjunto de configuraciones, en primer lugar, cuestionaría el uso de una base de datos relacional; no agrega ningún beneficio evidente sobre los archivos de configuración en el sistema de archivos. No puede usar fácilmente el control de versiones para sus archivos de configuración y gestionar las diferencias ambientales (por ejemplo, entornos "DEV", "TEST" y "PRODUCTION") ahora requiere una GUI para modificar la base de datos (¡ay, cómo se conecta al base de datos en primer lugar?).

Si su aplicación necesita "razonar" sobre la configuración como un todo, por ejemplo, si tiene una solución de múltiples usuarios y necesita configurar dinámicamente la aplicación en función del sistema actual, le sugiero que almacene los archivos de configuración como un documento de texto en su base de datos, con los metadatos que permiten a la aplicación almacenar / recuperar el documento. Diferentes motores de base de datos tienen diferentes soluciones para almacenar documentos de texto. Por ejemplo, en un sistema de tenencia múltiple, es posible que tenga:

ID client_id valid_from     valid_until configuration_file
-------------------------------------------------------
1         1   2016/03/16         NULL      <<DOCUMENT>>

Esto le permitiría recuperar el archivo para el cliente 1, que era válido después del 3 de marzo, y hacer lo que necesite la aplicación.

Si su aplicación necesita razonar sobre el contenido de la configuración, no la configuración como una entidad en sí misma, tiene un problema diferente. La solución de "nombre / valor" que usted propone también se conoce como Entidad / Atributo / Valor (EAV), y hay lots questions of SO sobre beneficios y desventajas. TL; DR: es difícil convertir preguntas incluso simples a SQL cuando se usa EAV.

Es mucho más fácil consultar los datos si cada configuración es una columna, con el tipo de datos apropiado. Pero esto significa que terminas con una tabla muy "amplia" (la mayoría de las aplicaciones tienen docenas o incluso cientos de valores de configuración), y cada vez que quieres agregar una configuración, terminas modificando el esquema de tu base de datos, que no es t práctico.

La alternativa, entonces, es almacenar los valores de configuración como un documento estructurado: XML y JSON son ampliamente compatibles. Estos formatos pueden ser consultados por el motor de la base de datos, pero no requieren un esquema fijo.

Answer 2

Aquí blogueo acerca de cuándo movimos nuestros AppSettings a una tabla de base de datos. El rendimiento no es un problema, ya que solo se extrae una vez al inicio de la aplicación y se almacena en un diccionario para facilitar la búsqueda.

No estoy seguro acerca de su aplicación, pero la razón importante por la que lo hicimos ahora es que es imposible utilizar los valores de producción si está en desarrollo, prueba, etc.

Answer 3

Creo que el diseño de 2 columnas (nombre, valor) es mucho mejor. Como dijo, si necesita agregar una nueva propiedad, todo lo que necesita hacer es " insert " una nueva fila. Mientras esté en el otro diseño (una sola fila), tendrá que cambiar el esquema de la tabla para agregar una columna para la nueva propiedad.

Esto, sin embargo, depende de si su lista de propiedades va a cambiar en el futuro.

Answer 4

DESPRECIO poner valores que no sean cadenas en una columna de cadena (aka, tipos de datos incorrectos). (Como dice @Kenny Evitt arriba)

Así que se me ocurrió la alternativa a continuación que va en vertical Y trata con los tipos de datos correctos.

En realidad, no uso dinero y smallmoney. Pero los incluí para completar. Tenga en cuenta que hay otros tipos de datos por ahí

ver

https://msdn.microsoft.com/en-us/library/ms187752.aspx?f=255&MSPPError=-2147217396

Pero el siguiente cubre la mayoría de las cosas.

para ser honesto, solo uso string (varchar (1024)), int, smallint y bit ... el 99% del tiempo.

No es perfecto Aka, tienes muchas tuplas nulas. Pero como solo tomas estos una vez (y el caché), la asignación a un objeto de configuración (en c # en mi mundo) no es difícil.

CREATE TABLE [dbo].[SystemSetting](
[SystemSettingId] [int] IDENTITY NOT NULL,

[SettingKeyName] [nvarchar](64) NOT NULL, 
[SettingDataType] [nvarchar](64) NOT NULL, /* store the datatype as string here */

[SettingValueBigInt] bigint NULL, 
[SettingValueNumeric] numeric NULL, 
[SettingValueSmallInt] smallint NULL, 
[SettingValueDecimal] decimal NULL, 
[SettingValueSmallMoney] smallmoney NULL, 
[SettingValueInt] int NULL, 
[SettingValueTinyInt] tinyint NULL, 
[SettingValueMoney] money NULL, 
[SettingValueFloat] float NULL, 
[SettingValueReal] real NULL, 
[SettingValueDate] date NULL, 
[SettingValueDateTimeOffSet] datetimeoffset NULL, 
[SettingValueDateTime2] datetime2 NULL, 
[SettingValueSmallDateTime] smalldatetime NULL, 
[SettingValueDateTime] datetime NULL, 
[SettingValueTime] time NULL, 
[SettingValueVarChar] varchar(1024) NULL, 
[SettingValueChar] char NULL, 

[InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
[InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
[LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
[LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    

)

Ahora, si eso es demasiado, y está decidido a usar "cadenas" para todos los valores, entonces aquí hay algunos DDL.

DROP TABLE [dbo].[SystemSetting]
DROP TABLE [dbo].[SystemSettingCategory]

CREATE TABLE [dbo].[SystemSettingCategory] (
    [SystemSettingCategoryId] [int] NOT NULL,
    [SystemSettingCategoryName] [nvarchar](64) NOT NULL, 
    [InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
    [InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
    [LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
    [LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    
    CONSTRAINT [PK_SystemSettingCategory] PRIMARY KEY CLUSTERED ([SystemSettingCategoryId] ASC),
    CONSTRAINT UQ_SystemSettingCategoryName UNIQUE NONCLUSTERED ([SystemSettingCategoryName])
)   




CREATE TABLE [dbo].[SystemSetting] (
    [SystemSettingId] [int] NOT NULL,
    [SystemSettingCategoryId] INT NOT NULL,     /* FK to [SystemSettingCategory], not shown here */
    [SettingKeyName] [nvarchar](64) NOT NULL, 
    [SettingValue] nvarchar(1024) NULL,
    [InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
    [InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
    [LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
    [LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    
    CONSTRAINT [PK_SystemSetting] PRIMARY KEY CLUSTERED ([SystemSettingId] ASC),
    CONSTRAINT FK_SystemSettingCategory_SystemSettingCategoryId foreign key ([SystemSettingCategoryId]) references [SystemSettingCategory] ([SystemSettingCategoryId]),
    CONSTRAINT UQ_SystemSettingCategoryId_SettingKeyName UNIQUE NONCLUSTERED ( [SystemSettingCategoryId] , [SettingKeyName] )
)   



INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 101 , ''EmployeeSettings'' UNION ALL select 201, ''StopLightSettings''

INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 1001 , 101 , ''MininumAgeRequirementMonths'' , convert(varchar(16) , (12 * 18))
UNION ALL select 1002 , 101 , ''MininumExperienceMonths'' , convert(varchar(8) , 24)
UNION ALL select 2001 , 201 , ''RedLightPosition'' , ''top''
UNION ALL select 2002 , 201 , ''YellowLightPosition'' , ''middle''
UNION ALL select 2003 , 201 , ''GreenLightPosition'' , ''bottom''

/* should fail */
/* start 
INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 3333 , ''EmployeeSettings''
INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 101 , ''xxxxxxxxxxxxxx''
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 5555 , 101 , ''MininumAgeRequirementMonths'' , 555
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 1001 , 101 , ''yyyyyyyyyyyyyy'' , 777
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 5555 , 555 , ''Bad FK'' , 555
 end */


Select * from [dbo].[SystemSetting] where [SystemSettingCategoryId] = 101 /* employee related */
Select * from [dbo].[SystemSetting] where [SystemSettingCategoryId] = 201 /* StopLightSettings related */

Ahora, yendo un poco más lejos, aún puede crear objetos dotnet fuertemente tipificados con los tipos de datos correctos, y luego convertir su datareader / dataset en el objeto fuerte como se ve a continuación.

public class EmployeeSettings
{
    public Int16 MininumAgeRequirementMonths { get; set; }
    public Int16 MininumExperienceMonths{ get; set; }
}


public class StopLightSettings
{
    public string RedLightPosition { get; set; }
    public string YellowLightPosition { get; set; }
    public string GreenLightPosition { get; set; }
}

Todavía puede hacer las clases C # (o cualquier idioma) ........ y utilizar el método SettingDataType anterior. El código de "mapeo" solo necesita un poco de trabajo adicional.

Cuando no está fuera de votación, uso las clases SettingDataType y C # como se ve arriba.

Answer 5

El primer problema que debe considerar es el siguiente: deje de pensar en la eficacia de recuperar la información. en primer lugar, descubra cómo modelar de manera efectiva y correcta los datos y luego (y solo entonces) descubra cómo hacerlo de manera eficiente .

Por lo tanto, depende de la naturaleza de los datos de configuración que está almacenando. Si los pares separados (nombre, valor) básicamente no están relacionados, guárdelo como uno por fila. Si están relacionados, puede considerar un esquema que tenga múltiples columnas.

¿Qué quiero decir con relacionado? Considera alguna configuración de caché. Cada caché tiene varios atributos:

• política de desalojo;
• tiempo de expiración;
• talla máxima.

Supongamos que cada caché tiene un nombre. Puede almacenar estos datos en tres filas:

• <name>_EVICTION
• <name>_EXPIRY
• <name>_MAX_SIZE

pero esta información está relacionada y a menudo tendrá que recuperarlos todos a la vez. En ese caso, puede tener sentido tener una tabla cache_config con cinco columnas: id, name, eviction, expiry, max_size.

Eso es lo que quiero decir con datos relacionados.

Answer 6

He usado ambos métodos y prefiero el método de 2 columnas. El retroceso a la nueva columna para cada configuración es que necesita cambiar el código para agregar nuevas configuraciones.

Prefiero usar la columna One por método de configuración (cuando estoy accediendo al valor). Esto se debe a que los ajustes de configuración están más explícitamente establecidos. Pero esa preferencia no supera la dificultad de agregar una nueva configuración a la mesa.

Yo recomendaría el método de 2 columnas. Luego configure una función de acceso / sproc para obtener los valores.

Answer 7

Para datos de configuración, utilizaría la estructura clave / valor con una fila por entrada de configuración. Es probable que lea estos datos una vez y los guarde en caché, por lo que el rendimiento no es un problema. Como usted señala, agregar columnas cada vez que cambia el conjunto de teclas de configuración requiere mucho más mantenimiento.

SQL sobresale al modelar y manipular arbitrariamente grandes conjuntos de datos estructurados similar (si no es el mismo). Un conjunto de información de configuración realmente no es eso: usted tiene una sola fila de datos O usted tiene múltiples filas de datos completamente no relacionados. Eso dice que solo estás usando esto como una tienda de datos. Digo omitir el modelo de datos SQL e ir simple.

Answer 8

Puede guardar la configuración de manera eficiente utilizando XML. Algunas bases de datos admiten la función XML puro en la que puede guardar el valor como tipo de datos xml y puede ejecutar XQUERY en esa columna en particular.

Crea una tabla con dos nombres de columna y configuración. nombre con tipo de datos de cadena y configuración con tipo de datos xml para que no tenga que preocuparse por la inserción y eliminación de nuevos parámetros de configuración, solo tendrá una nueva etiqueta en xml. Y si la base de datos no es compatible con XML, simplemente guárdelo como una cadena, pero en formato XML para que pueda analizar esa configuración de forma manual o utilizando alguna API de manera eficiente.

Creo que esta sería una mejor manera en lugar de almacenar la configuración completa como una cadena.

Answer 9

Una consideración más: con una columna para cada parámetro de configuración, puede tener fácilmente versiones. Cada fila representa una versión.

Answer 10

Una desventaja de utilizar una fila separada para cada configuración de aplicación (aplicación) (o la opción de aplicación) es que no puede almacenar los valores de configuración en una columna con un tipo de datos apropiado. ¿Pueden los usuarios ingresar datos con un tipo no válido? ¿Es pertinente para su aplicación?

Un beneficio del uso de columnas separadas es que cualquier código en su DB mismo (por ejemplo, procedimientos almacenados, funciones, etc.) puede usar un valor del tipo de datos apropiado sin primero tener que verificar valores inválidos y luego convertir al tipo de datos apropiado .

Si está implementando manualmente cambios en su base de datos de aplicaciones, entonces sí, si está utilizando un diseño de EAV, es muy ligeramente más fácil implementar nuevas configuraciones, pero realmente ¿para qué sirve el ahorro?

INSERT Options ( ConfigurationSetting, Value )
VALUES ( ''NewConfigurationSetting'', NewConfigurationSettingValue )

versus:

ALTER TABLE Options ADD NewConfigurationSetting some_datatype

UPDATE Options
SET NewConfigurationSetting = NewConfigurationSettingValue

Answer 11

depende

Si tiene menos de 15 valores, haría una columna para cada uno.

Si cambia la cantidad de configuraciones regularmente, o si a menudo no usa todas las configuraciones, consideraría hacer una fila por configuración.

Más allá de eso, es probable que sea una sacudida. Depende de tus patrones de uso. Si siempre necesita tomar todas las configuraciones, probablemente sea más rápido tenerlas en una fila.

Agregar columnas no es muy difícil, y si programa de manera sensata, generalmente no tiene que actualizar ninguno de sus otros códigos.

Answer 12

CREATE TABLE Configuration (
    Name ...,
    Value ...,
);

La mejor manera. Agregar una columna a una tabla normalmente es una mierda, y ¿para qué sirve una tabla con una fila?

No estoy seguro de que esto sea apropiado para SQL, pero por desgracia ... pregunta respondida.