Hej.

Ställdes för en tid sedan inför ett problem som eventuellt andra har stött på, och kanske löst bättre än jag, så jag tänkte att det kanske fanns ett allmänt intresse för den lösningen jag gjort.

Problem :
I ett system som hela tiden byggs ut och som bygger på SQL Server, så tillkommer vissa parametrar under tidens gång. Vissa enbart för en viss kund, andra enbart för en separat funktion som endast en mindre grupp kunder använder. Att i detta läge lägga till ett fält i en tabell som håller systemparametrar kan vara att slösa med resurser, då 99% av posterna kommer att innehålla NULL eller tomt.

Lösning:
Gör en multitabell. Ok, ordet multitabell har jag själv hittat på, i brist på bättre namn ...
Tanken med en multitabell är att en tabell ska innehålla data för flera "pseudo-tabeller". Sättet att åstadkomma detta på är att bryta mot allt vad databasstandard heter och istället lägga upp en tabell enligt följande :

CREATE TABLE [dbo].[myMultiTable](
[record_no] [int] IDENTITY(1,1) NOT NULL,
[companyId] [varchar](30) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[fieldname] [varchar](30) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[value] [varchar](30) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[tableName] [varchar](30) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[description] [varchar](200) COLLATE Finnish_Swedish_CI_AS NULL
) ON [PRIMARY]

OBS : Den exakta definitionen av fälten med längd och collation kan givetvis ändras till ditt specifika behov utan att funktionen på något sätt ändras.

Vad vi nu har är en tabell där data exempelvis kan lagras enligt följande :
record_no companyId fieldname value tableName description
3 1001 BatchNo H ScanCodePrefix Styrtecken för fältet BatchNo
4 1001 Quantity Q ScanCodePrefix Styrtecken för fältet Quantity
6 1001 Supplier V ScanCodePrefix Styrtecken för fältet Supplier
...
32 1003 Machine 5405 AvailableMachines Namn på tillgänglig maskin
33 1003 Machine 5406 AvailableMachines Namn på tillgänglig maskin
34 1003 Machine 5407 AvailableMachines Namn på tillgänglig maskin

Vad vi bland annat här kan utläsa är att företaget med ID 1001 tydligen använder något styrtecken när det ska skannas batchnummer,
och att företag 1003 har en maskin som heter 5405 som ingår i systemet.

Om jag nu kommer på att företag 1003 kommer att använda en helt ny funktion, kallad "specialfunktion1" (SF1) så kanske jag vill indikera detta genom att skapa följande post :
record_no companyId fieldname value tableName description
34 1003 SF1 1 special_functions Den nya funktionen som ...

Jag har då åstadkommit att jag fått tillgång till en ny parameter bara genom att skapa en ny post i tabellen. Sen är det bara att använda värdet som du själv behagar. :-)

Jag är dock redan nu medveten om ett par brister i lösningen, nämligen :
1) Det är knepigt att göra länkningar till andra tabeller via denna multitabell.
2) SQL-satser mot denna tabell blir lite annorlunda jämfört med "vanliga" tabeller.
3) Som utvecklare får du alltid 0 rader i svar då du frågar tabellen efter något som inte finns, till skillnad från ett NULL värde.
(Om man exempelvis frågar efter fältet "Nisse" i multitabellens tabell "Nisses_tabell" för företaget med ID 9999, så ges 0 rader som svar,
då varken tabellen, fältet eller ID:t är upplagt. Hade fältet funnits i en vanlig tabell så hade antagligen resultatet blivit NULL istället, givet att
övrig data funnits.)


Synpunkter och åsikter mottages tacksamt.

Med vänlig hälsning

Jonas

En variant är att lägga upp ett schema per företag och låta dem gå bananas med tabeller inom sitt eget schema.
T.ex

CREATE SCHEMA [Customized.ACME]
CREATE TABLE [Customized.ACME].MachineNames (...)
CREATE TABLE [Customized.ACME].Widgets (...)

CREATE SCHEMA [Customized.Megasoft]
CREATE TABLE [Customized.Megasoft].MachineNames (...)
CREATE TABLE [Customized.Megasoft].Sprockets (...)

På så sätt slipper du de brister du identifierat - du kan skapa foreign keys, defaultvärden, SP:ar och funktioner i vanlig ordning. Dessutom får du möjlighet att ex.vis. skapa användare som bara har läs/skrivrättigheter på ett specifikt schema. Nackdelen blir förstås att det kan bli en sabla massa tabeller att skapa och hålla ordning på :)

Tack för dina synpunkter Tomas.

Jag kan inte påstå att jag använt SCHEMA särskilt mycket tidigare, (läs:"inte alls") så jag är inte så påläst på vilka för/nackdelar det upplägget har.
Det du beskriver känns i mitt fall som "overkill" då jag alltid är intresserad av svaret på frågan :
"Har företag X inställningen Y och i såfall, vilket värde har denna inställning?"

Systemet ändrar då sitt beteende utefter det värde som angivits.
Jag gör aldrig JOIN's mot denna tabell, eller bryr min om säkerheten då systemet har en egen användare som är den enda användaren som har rättigheter till tabellen.

Om man väljer att använda tabellen till enbart detta syfte, tror du då att det finns någon bättre lösning?

Jag skulle nog vilja lägga över inställningsdefinitionerna i en egen tabell i varje fall, så du slipper repetera fältnamn/kommentar för varje ställe där inställningen används:

-- Den här tabellen representeras förslagsvis som en enum, där varje post i tabellen motsvarar en medlem i enumen.
CREATE TABLE SettingType(
	SettingTypeId INT IDENTITY(1,1) NOT NULL,
	Name NVARCHAR(50) NOT NULL,
	[Type] NVARCHAR(50) NOT NULL,
	PRIMARY KEY(SettingTypeId)
)

CREATE TABLE Setting(
	SettingId INT IDENTITY(1,1) NOT NULL,
	Name NVARCHAR(200) NOT NULL,
	SettingTypeId INT NOT NULL,
	[Description] NVARCHAR(MAX),
	PRIMARY KEY(SettingId),
	CONSTRAINT FK_Setting_SettingType FOREIGN KEY (SettingTypeId) REFERENCES SettingType(SettingTypeId)
)

CREATE TABLE CompanySetting(
	CompanySettingId INT IDENTITY(1,1) NOT NULL,
	SettingId INT NOT NULL,
	CompanyId INT NOT NULL,
	Value NVARCHAR(MAX),
	PRIMARY KEY(CompanySettingId),
	CONSTRAINT FK_CompanySettings_Settings FOREIGN KEY (SettingId) REFERENCES Setting(SettingId)
)
	

SET IDENTITY_INSERT SettingType ON
INSERT INTO SettingType(SettingTypeId, Name, [Type])
VALUES
(1, 'Integer', 'int'),
(2, 'String', 'nvarchar'),
(3, 'DateTime', 'datetime'),
(4, 'Boolean', 'bit')
SET IDENTITY_INSERT SettingType OFF

SET IDENTITY_INSERT Setting ON
INSERT INTO Setting(SettingId, Name, SettingTypeId, [Description])
VALUES
(1, 'FCOLOR', 2, 'Favoritfärg'),
(2, 'ShoeSize', 1, 'Skostorlek')
SET IDENTITY_INSERT Setting OFF

INSERT INTO CompanySetting(SettingId, CompanyId, Value)
VALUES
(1, 1, 'Citronblå'),
(1, 2, 'Kommunistgrön'),
(2, 1, '42')

Sen kan du köra nåt i stil med följande för att få ut det hela i ungefär samma format som du var inne på:

SELECT cs.CompanySettingId, cs.CompanyId, s.Name, cs.Value, st.Name, s.[Description]
FROM CompanySetting cs 
	INNER JOIN Setting s ON cs.SettingId = s.SettingId
	INNER JOIN SettingType st ON s.SettingTypeId = st.SettingTypeId

Snyggt!

Tror nog din föreslagna lösning är bättre än min ursprungliga i flera avseenden. Visst, man får fler tabeller att hantera, men de är ändå ett fast antal, med mer inbyggd flexibilitet än mitt första förslag. Ska kika närmare på din kod vid ett senare tillfälle, strax hägrar semestern!
(Heh, ja, jag har en extremt sen semester, jag vet ....)

Nåväl, oavsett, tack för din tid, det är lätt att bli lite "hemmablind" när man har ett problem framför sig som man grubblat på en del.

Jonas

Kul att ni kom fram till en bra lösning, lycka till!

//Pelle