Odziedziczyłem aplikację, która łączy wiele różnych rodzajów działań z witryną. Istnieje około 100 różnych rodzajów aktywności, a każdy z nich ma inny zestaw 3-10 pól. Jednak wszystkie działania mają co najmniej jedno pole daty (może to być dowolna kombinacja daty, daty rozpoczęcia, daty zakończenia, zaplanowanej daty rozpoczęcia itp.) Oraz jedno pole osoby odpowiedzialnej. Wszystkie pozostałe pola różnią się znacznie, a pole daty początkowej niekoniecznie będzie nosiło nazwę „Data początkowa”.
Utworzenie jednej tabeli podtypów dla każdego rodzaju działania skutkowałoby schematem zawierającym 100 różnych tabel podtypów, co byłoby zbyt dziwne, aby sobie z tym poradzić. Obecnym rozwiązaniem tego problemu jest przechowywanie wartości aktywności jako par klucz-wartość. Jest to znacznie uproszczony schemat obecnego systemu, aby uzyskać punkt.
Każde działanie ma wiele pól ActivityField; każda witryna ma wiele działań, a tabela SiteActivityData przechowuje KVP dla każdej SiteActivity.
To sprawia, że aplikacja (internetowa) jest bardzo łatwa do kodowania, ponieważ wszystko, co naprawdę musisz zrobić, to zapętlić rekordy w SiteActivityData dla danego działania i dodać etykietę i kontrolę wejściową dla każdego wiersza do formularza. Ale jest wiele problemów:
- Uczciwość jest zła; możliwe jest umieszczenie pola w SiteActivityData, które nie należy do typu działania, a DataValue jest polem varchar, więc liczby i daty muszą być stale rzutowane.
- Raporty i zapytania ad hoc tych danych są trudne, podatne na błędy i powolne. Na przykład uzyskanie listy wszystkich działań określonego typu, które mają datę końcową w określonym zakresie, wymaga osi przestawnych i rzutowania varcharów na daty. Autorzy raportu NIENAWIDZĄ tego schematu i nie winię ich.
Tak więc szukam sposobu na przechowywanie dużej liczby działań, które prawie nie mają wspólnych pól w sposób, który ułatwia raportowanie. Do tej pory wymyśliłem XML do przechowywania danych aktywności w formacie pseudo-noSQL:
Tabela aktywności zawierałaby XSD dla każdego działania, eliminując potrzebę korzystania z tabeli ActivityField. SiteActivity będzie zawierał klucz-wartość XML, więc każde działanie dla witryny będzie teraz w jednym wierszu.
Aktywność wyglądałaby mniej więcej tak (ale nie w pełni ją rozwinąłem):
<SomeActivityType>
<SomeDateField type="StartDate">2000-01-01</SomeDateField>
<AnotherDateField type="EndDate">2011-01-01</AnotherDateField>
<EmployeeId type="ResponsiblePerson">1234</EmployeeId>
<SomeTextField>blah blah</SomeTextField>
...
Zalety:
- XSD sprawdziłby poprawność XML, wychwytując błędy takie jak wstawianie ciągu znaków w polu liczbowym na poziomie bazy danych, co było niemożliwe w starym schemacie, który przechowywał wszystko w varchar.
- Zestaw rekordów KVP używanych do budowy formularzy internetowych można łatwo odtworzyć przy użyciu
select ... from ActivityXML.nodes('/SomeActivityType/*') as T(r) - Podkwerenda xpath w pliku XML może zostać użyta do wygenerowania zestawu wyników zawierającego kolumny dla daty początkowej, końcowej itp. Bez użycia osi przestawnej, coś w rodzaju
select ActivityXML.value('.[@type=StartDate]', 'datetime') as StartDate, ActivityXML.value('.[@type=EndDate]', 'datetime') as EndDate from SiteActivity where...
Czy to wydaje się dobrym pomysłem? Nie mogę wymyślić innych sposobów przechowywania tak dużej liczby różnych zestawów właściwości. Inną myślą, którą miałem, było zachowanie istniejącego schematu i przetłumaczenie go na coś łatwiejszego do zapytania w hurtowni danych, ale nigdy wcześniej nie projektowałem schematu gwiazdy i nie mam pojęcia, od czego zacząć.
Dodatkowe pytanie: jeśli zdefiniuję znacznik jako typ danych daty w XSD za pomocą xs:date, czy SQL Server będzie indeksował go jako wartość daty? Obawiam się, że jeśli zapytam według daty, będzie musiał rzucić ciąg daty na wartość daty i zniszczyć każdą szansę na użycie indeksu.
