Исправление плохого дизайна базы данных BAD после того, как данные находятся в системе

Почему вы хотите сделать это на SQL? Просто напишите небольшой скрипт, выполняющий преобразование.

Вы, вероятно, захотите создать заменяющие таблицы на месте. Тип сценария зависит от доступного вам языка сценариев, но вы должны иметь возможность создать таблицу идентификаторов страны, просто перечислив столбцы таблицы, которая у вас есть сейчас. Как только вы это сделаете, вы можете выполнить некоторые замены строк, чтобы просмотреть все уникальные названия стран и вставить их в таблицу разновидностейFoundInCountry, в которой столбец данной страны не равен нулю.

Я бы использовал сценарий для генерации всех индивидуальных запросов, поскольку это однократный процесс импорта.

Некоторые программы, такие как Excel, хорошо умеют смешивать данные разных измерений (сравнивая имена столбцов с данными внутри строк), но реляционные базы данных редко.

Однако вы можете обнаружить, что некоторые системы (например, как ни странно, Microsoft Access) имеют удобные инструменты, которые можно использовать для нормализации данных. Лично я считаю, что написать сценарий быстрее, но ваши относительные навыки работы с доступом и написанием сценариев могут отличаться от моих.

Когда я сталкиваюсь с ними, я пишу сценарий для преобразования, а не пытаюсь сделать это в SQL. Для меня это обычно намного быстрее и проще. Выберите любой язык, который вам удобен.

Вы, вероятно, могли бы сообразить и запросить системные таблицы для имен столбцов, а затем построить динамическую строку запроса для выполнения, но, честно говоря, это, вероятно, будет уродливее, чем быстрый сценарий для генерации операторов SQL для вас.

Надеюсь, у вас не так много динамического кода SQL, который обращается к старым таблицам, скрытым в вашей кодовой базе. Это может быть самая сложная часть В самом деле.

В SQL Server это сгенерирует ваш собственный выбор, который вы продемонстрируете. Вы можете экстраполировать на вставку

select 
  'SELECT Species.ID, Country.CountryID FROM Country, Species WHERE (((Species.' + 
 c.name + 
 ')=1)) AND (((Country.Country) = "' +
 c.name + 
 '"))'
from syscolumns c
inner join sysobjects o
on o.id = c.id
where o.name = 'old_table_name'

Как и в случае с другими, я, скорее всего, просто сделаю это как одноразовое быстрое исправление любым способом, который вам подходит.

С этими типами преобразований они являются одноразовыми элементами, быстрыми исправлениями, и код не должен быть элегантным, он просто должен работать. Для подобных вещей я делал это разными способами.

Если это SQL Server, вы можете использовать таблицу sys.columns, чтобы найти все столбцы исходной таблицы. Затем вы можете использовать динамический SQL и команду pivot, чтобы делать то, что вы хотите. Поищите синтаксис в Интернете.

Я определенно согласен с вашим предложением написать небольшой скрипт для создания вашего SQL с запросом для каждого столбца.

Фактически, ваш сценарий мог быть уже закончен за то время, которое вы потратили на размышления об этом волшебном запросе (который вы использовали бы только один раз, а затем выбросили бы, так что какой толк в том, чтобы все это было волшебным и совершенным)

Если бы это был SQL Server, вы бы использовали команды Unpivot, но глядя на назначенный вами тег для доступа - я прав?

Хотя есть поворотная команда в доступе, обратного оператора нет.

Похоже, это можно сделать с помощью сложного соединения. Проверьте этот интересная статья, чтобы узнать, как отменить поворот в команде выбора.

Я бы сделал это трехэтапным процессом с небольшими временными изменениями в вашей таблице SpeciesFoundInCountry. Я бы добавил в эту таблицу столбец для хранения названия страны. Тогда шаги будут следующими.

1) Создайте / запустите сценарий, который просматривает столбцы в исходной таблице и создает запись в SpeciesFoundInCountry для каждого столбца, имеющего истинное значение. Эта запись будет содержать название страны. 2) Запустите инструкцию SQL, которая обновляет поле SpeciesFoundInCountry.CountryID, присоединяясь к таблице Country в названии страны. 3) Очистите таблицу SpeciesFoundInCountry, удалив столбец CountryName.

Вот небольшой псевдокод MS Access VB / VBA, чтобы дать вам суть

Public Sub CreateRelationshipRecords()

  Dim rstSource as DAO.Recordset
  Dim rstDestination as DAO.Recordset
  Dim fld as DAO.Field
  dim strSQL as String
  Dim lngSpeciesID as Long

  strSQL = "SELECT * FROM [ORIGINALTABLE]"
  Set rstSource = CurrentDB.OpenRecordset(strSQL)
  set rstDestination = CurrentDB.OpenRecordset("SpeciesFoundInCountry")

  rstSource.MoveFirst

  ' Step through each record in the original table
  Do Until rstSource.EOF
    lngSpeciesID = rstSource.ID
    ' Now step through the fields(columns). If the field
    ' value is one (1), then create a relationship record
    ' using the field name as the Country Name
    For Each fld in rstSource.Fields
      If fld.Value = 1 then
        with rstDestination
          .AddNew
          .Fields("CountryID").Value = Null
          .Fields("CountryName").Value = fld.Name
          .Fields("SpeciesID").Value = lngSpeciesID
          .Update
        End With
      End IF
    Next fld  
    rstSource.MoveNext
  Loop

  ' Clean up
  rstSource.Close
  Set rstSource = nothing
  ....

End Sub

После этого вы можете запустить простой оператор SQL для обновления значений CountryID в таблице SpeciesFoundInCountry.

ОБНОВЛЕНИЕ SpeciesFoundInCountry ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ Country ON SpeciesFoundInCountry.CountryName = Country.CountryName SET SpeciesFoundInCountry.CountryID = Country.CountryID;

Наконец, все, что вам нужно сделать, это очистить таблицу SpeciesFoundInCountry, удалив столбец CountryName.

**** СТОРОННЕЕ ПРИМЕЧАНИЕ: Я счел полезным иметь таблицы стран, которые также включают сокращения ISO (коды стран). Иногда они используются в качестве внешних ключей в других таблицах, поэтому соединение с таблицей Country не нужно включать в запросы.

Для получения дополнительной информации: http://en.wikipedia.org/wiki/Iso_country_codes

Извините, но кровавый парсер сообщений удалил пробелы и форматирование в моем сообщении. Это затрудняет чтение журнала.

@stomp:

Над полем, в котором вы вводите ответ, есть несколько кнопок. Тот, который равен 101010, представляет собой образец кода. Вы выделяете весь текст, представляющий собой код, и затем нажимаете эту кнопку. Тогда это не сильно испортится.

cout>>"I don't know C"
cout>>"Hello World"

Я бы использовал запрос Union, очень грубо:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb

Set tdf = db.TableDefs("SO")

strSQL = "SELECT ID, Species, """ & tdf.Fields(2).Name _
    & """ AS Country, [" & tdf.Fields(2).Name & "] AS CountryValue FROM SO "

For i = 3 To tdf.Fields.Count - 1
    strSQL = strSQL & vbCrLf & "UNION SELECT ID, Species, """ & tdf.Fields(i).Name _
    & """ AS Country, [" & tdf.Fields(i).Name & "] AS CountryValue FROM SO "
Next

db.CreateQueryDef "UnionSO", strSQL

Тогда у вас будет представление, которое можно добавить к вашему новому дизайну.

Когда я прочитал заголовок «Плохой ПЛОХОЙ дизайн базы данных», мне было любопытно узнать, насколько это плохо. Вы меня не разочаровали :)

Как уже упоминалось, сценарий был бы самым простым способом. Этого можно добиться, написав около 15 строк кода на PHP.

SELECT * FROM ugly_table;
while(row)
foreach(row as field => value)
if (value == 1)
SELECT country_id from country_table WHERE country_name = field;

if (field == 'Species')
SELECT species_id from species_table WHERE species_name = value;

INSERT INTO better_table (...)

Очевидно, что это псевдокод, и он не будет работать как есть. Вы также можете заполнить таблицу стран и видов "на лету", добавив сюда операторы вставки.

Извините, я очень мало занимался программированием Access, но могу предложить некоторые рекомендации, которые должны помочь.

Сначала давайте рассмотрим проблему. Предполагается, что вам обычно потребуется сгенерировать несколько строк в SpeciesFoundInCountry для каждой строки в исходной таблице. Другими словами, виды обычно обитают более чем в одной стране. На самом деле это легко сделать с декартовым произведением, соединением без критериев соединения.

Чтобы создать декартово произведение, вам необходимо создать таблицу Country. В таблице должен быть country_id от 1 до N (N - количество уникальных стран, около 200) и название страны. Чтобы упростить жизнь, просто используйте цифры от 1 до N в порядке столбцов. Это сделало бы Афганистан 1 и Албанию 2 ... Зимбабве N. Вы должны иметь возможность использовать для этого системные таблицы.

Затем создайте таблицу или представление из исходной таблицы, которая содержит виды и жало с 0 или 1 для каждой страны. Вам нужно будет преобразовать null, а не null в текст 0 или 1 и объединить все значения в одну строку. Описание таблицы и текстовый редактор с регулярными выражениями должны упростить эту задачу. Сначала поэкспериментируйте с одним столбцом, а когда он заработает, отредактируйте вид / вставку для создания всех столбцов.

Затем объедините две таблицы вместе без критериев объединения. Это даст вам запись для каждого вида в каждой стране, вы почти у цели.

Теперь все, что вам нужно сделать, это отфильтровать недопустимые записи, у них будет ноль в соответствующем месте в строке. Поскольку столбец country_code таблицы стран имеет расположение подстроки, все, что вам нужно сделать, это отфильтровать записи, где это 0.

where substring(new_column,country_code) = '1'

Вам все равно нужно будет создать таблицу видов и присоединиться к ней.

where a.species_name = b.species_name

a и b - псевдонимы таблиц.

Надеюсь на эту помощь

OBTW,

Если у вас есть запросы, которые уже выполняются к старой таблице, вам нужно будет создать представление, которое реплицирует старые таблицы с использованием новых таблиц. Вам нужно будет сделать группу по, чтобы денормализовать таблицы.

Сообщите своим пользователям, что старая таблица / представление не будет поддерживаться в будущем, и все новые запросы или обновления старых запросов должны будут использовать новые таблицы.

Если мне когда-либо придется создать грузовик с похожими операторами SQL и выполнить их все, я часто обнаруживаю, что Excel очень удобен. Возьмите исходный запрос. Если у вас есть список стран в столбце A и ваш оператор SQL в столбце B, оформленный в виде текста (в кавычках) со ссылками на ячейки, вставленными там, где страна появляется в sql

например = "ВСТАВИТЬ В новую_таблицу SELECT ... (разновидности." & A1 & ") = ...));"

затем просто скопируйте формулу, чтобы создать 200 различных операторов SQL, скопируйте / вставьте столбец в свой редактор и нажмите F5. Конечно, вы можете сделать это с любым количеством переменных.

Это (надеюсь) разовое упражнение, поэтому неэлегантное решение может оказаться не таким плохим, как кажется.

Проблема (как, я уверен, вы слишком хорошо знаете!) В том, что в какой-то момент вашего запроса вы должны перечислить все эти столбцы. :( Вопрос в том, как это сделать наиболее элегантно? Вот моя попытка. Это выглядит громоздко, потому что столбцов очень много, но это может быть то, что вам нужно, или, по крайней мере, может указать вам на правильное направление.

Возможное решение SQL:

/* if you have N countries */
CREATE TABLE Country
(id    int, 
 name  varchar(50)) 

INSERT Country
      SELECT 1, 'Afghanistan'
UNION SELECT 2, 'Albania', 
UNION SELECT 3, 'Algeria' ,
UNION SELECT 4, 'American Samoa' ,
UNION SELECT 5, 'Andorra' ,
UNION SELECT 6, 'Angola' ,
...
UNION SELECT N-3, 'Western Sahara', 
UNION SELECT N-2, 'Yemen', 
UNION SELECT N-1, 'Zambia', 
UNION SELECT N, 'Zimbabwe', 



CREATE TABLE #tmp
(key        varchar(N),  
 country_id int) 
/* "key" field needs to be as long as N */  


INSERT #tmp 
SELECT '1________ ... _', 'Afghanistan' 
/* '1' followed by underscores to make the length = N */

UNION SELECT '_1_______ ... ___', 'Albania'
UNION SELECT '__1______ ... ___', 'Algeria'
...
UNION SELECT '________ ... _1_', 'Zambia'
UNION SELECT '________ ... __1', 'Zimbabwe'

CREATE TABLE new_table
(country_id int, 
species_id int) 

INSERT new_table
SELECT species.id, country_id
FROM   species s , 
       #tmp    t
WHERE  isnull( s.Afghanistan, ' ' ) +  
       isnull( s.Albania, ' ' ) +  
       ... +  
       isnull( s.Zambia, ' ' ) +  
       isnull( s.Zimbabwe, ' ' ) like t.key 

Мое предложение

Лично я бы этого не делал. Я бы сделал быстрое и грязное решение, подобное тому, на которое вы ссылаетесь, за исключением того, что я бы жестко закодировал идентификаторы стран (потому что вы собираетесь сделать это только один раз, верно? И вы можете сделать это сразу после создания таблица страны, чтобы вы знали, каковы все идентификаторы):

INSERT new_table SELECT Species.ID, 1 FROM Species WHERE Species.Afghanistan = 1 
INSERT new_table SELECT Species.ID, 2 FROM Species WHERE Species.Albania= 1 
...
INSERT new_table SELECT Species.ID, 999 FROM Species WHERE Species.Zambia= 1 
INSERT new_table SELECT Species.ID, 1000 FROM Species WHERE Species.Zimbabwe= 1 

Когда я столкнулся с подобными проблемами, я счел удобным сгенерировать сценарий, который генерирует сценарии SQL. Вот пример, который вы дали, в абстракции для использования% PAR1% вместо Афганистана.

SELECT Species.ID, Country.CountryID
FROM Country, Species
WHERE (((Species.%PAR1%)=1)) AND (((Country.Country) = "%PAR1%"))
UNION

Также было добавлено объединение ключевых слов как способ объединения всех выбранных элементов.

Далее вам понадобится список стран, созданный на основе ваших существующих данных:

Афганистан Албания . , .

Затем вам понадобится сценарий, который может перебирать список стран, и для каждой итерации вывести результат, в котором% PAR1% заменяется на Афганистан на первой итерации, на Албанию на второй и т. д. Алгоритм похож на слияние писем в текстовом редакторе. Написание этого сценария - небольшая работа. Но когда он у вас есть, вы можете использовать его в десятках разовых проектов, подобных этому.

Наконец, вам нужно вручную заменить последний «UNION» на точку с запятой.

Если вы можете заставить Access выполнить это гигантское объединение, вы можете получить нужные данные в нужной форме и вставить их в новую таблицу.