I attach here the English equivalents of the files I posted with my
initial message to the list earlier this year: the proposed ERM data
model, a SQL dump of a subset of the data, and a representation of the
ERM of that data structure generated from the MySQL DBMS.
The Council for British Archaeology
Company Limited by Guarantee, registered in England 1760254
Registered Charity 287815
• There's no table for UTM coordinates (Norway, Iceland, Denmark), but in any case the OSIE, OSGB and RT90 tables duplicate data. The lat/long coordinates can be converted into any of these algorithmically. I think the Coordinates table should have a field for the original grid reference but otherwise grid references should be generated from the lat/long coords.
• A table for different media associated with an inscription, including images, pdfs, etc.
• I've been told that it's good database practice to have the table names in singular forms (Object, Fragment, Bracteate, etc.)
Dr Tarrin Wills
Co-director, Centre for Scandinavian Studies
Lecturer in Early Scandinavian Studies — Departments of English & History
Coordinator, History Taught Postgraduate Programmes
School of Language and Literature, University of Aberdeen
King's College, Aberdeen, AB24 3UB, Scotland
Room: EIG2 or EIG4, 24 High St
Phone: +44 (0)1224 272411 or 272627 / Fax: 272624
> <rundata_2011-05-02.sql.bz2><Data Model.pdf><rundata.pdf>
The University of Aberdeen is a charity registered in Scotland, No SC013683.
> ï¿½ There's no table for UTM coordinates (Norway, Iceland, Denmark),
Excellent point! I did wonder if other coordinate systems were in
common use in those countries, but from what I could tell WGS84 geodetic
coordinates seemed to be the standard outside Sweden and the UK. But
UTM is indeed a good idea! So at your suggestion I've just added that
in now as a new table, for all coordinates (since it's a global system);
updated SQL dump and ERM attached. :)
> but in any case the OSIE, OSGB and RT90 tables duplicate data. The
> lat/long coordinates can be converted into any of these
> algorithmically. I think the Coordinates table should have a field
> for the original grid reference but otherwise grid references should
> be generated from the lat/long coords.
This is true. The storage of coordinates in different formats does
duplicate data, and in one sense it might be nice to either use a series
of views and functions in the DBMS to generate those representations on
the fly from the lat/lon data, or to do the same at a software level
when coordinate data is requested - for the website, say.
But in that case those calculations would be run over and again every
time the coordinates were requested. By storing them in the database
from the start, the transformation is performed only once and the
results then cached to be retrieved as needed. I suppose it's a trade
off between storage and cycles, and I happen to think the cached
approach is preferable.
But yes, the canonical representation should absolutely be the lat/lon
coordinates, and when it comes time to create data entry/update forms,
the derived representations can be calculated immediately at the point
at which new/updated coordinates are entered, to ensure that everything
remains in sync.
> ï¿½ A table for different media associated with an inscription,
> including images, pdfs, etc.
Yes, this will certainly become necessary, either as a separate table,
or as a development and expansion of the existing 'imagelinks' table,
which currently only contains URLs. How do you think such a table would
best work? Would URLs + captions/annotations be sufficient?
> ï¿½ I've been told that it's good database practice to have the table
> names in singular forms (Object, Fragment, Bracteate, etc.)
This is one of those perennially-raging computer-related debates, if
perhaps not *quite* as well-known as 'vi vs. emacs', 'Windows vs. Linux
vs. Mac', 'indentation and brace style' etc. ;) Both camps argue
tenaciously over which convention is superior... but really I think that
as long as you're *consistent*, it doesn't really matter all that much.
Neither form is more 'correct'.
Personally I think that 'SELECT latitude, longitude FROM coordinates'
reads more naturally than 'SELECT latitude, longitude FROM coordinate',
for example. Field names are singular as they're usually referred to
individually or in the context of individual records; table names are
plural because they represent collections of records and are usually
referred to in that context. But I'm not absolutely wedded to that
convention, if others have strong feelings in favour of singular forms