Displaying Normalised Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have inherited a database with a main table that holds about 100 fields. I
know what you're thinking - "glorified spreadsheet" - and you're absolutely
right. I have never had to normalise something this big before, so I was
looking for some opinions.

For example, I have three fields in the main table:
[Title]
[Subtitle]
[TitleShort]

I figure this should be made into another table, [Titles], with three
fields, [ID], [TitleType] and [Title], and link it to the main table. Does
this sound correct?

Furthermore, if all these fields are required for each record, how would you
display these on a form? Subform? I don't want users to have to enter the
[TitleType] every time. Form based on a cross-tab query?

I'd love to hear your thoughts.
 
Normalization is partly art and partly science. To be honest, I'm not sure I
agree with your analysis that those three fields need to be put in a second
table. If Subtitle and TitleShort are based on Title, then maybe. However, I
suspect that while there is a correlation among the three fields, you could
not generate Subtitle or TitleShort if you knew Title.

If you did separate them as you're proposing, you could create a query that
joins the Titles table to itself 3 times to return all 3 titles on a single
row:

SELECT Titles.ID, Titles.Title,
Subtitles.Title AS Subtitle,
Titles.Title AS TitleShort
FROM (Titles LEFT JOIN Titles AS Subtitles
ON Titles.ID = Subtitles.ID)
LEFT JOIN Titles AS TitleShorts
ON Titles.ID = TitleShorts.ID
WHERE (((Titles.TitleType)="Title")
AND ((Subtitles.TitleType)="Subtitle")
AND ((TitleShorts.TitleType)="TitleShort"));


Unfortunately, that query isn't updatable, but you could add a button beside
each row that opens an Update form that would let you change any of the 3
titles if you needed to.
 
Back
Top