Subject:
|
A sample Lego DB structure (long!)
|
Newsgroups:
|
lugnet.db.inv
|
Date:
|
Sat, 12 May 2001 03:46:23 GMT
|
Viewed:
|
880 times
|
| |
|
|
Hi:
I have been working on developing a relational DB to hold all of the
information I currently compile on sets and elements for my site.
Since I have no formal knowledge of DB theory it has been an interesting
project. By posting I hope to not only get your comments on problems with
the design but
also to perhaps compare this scheme with ones that others have been developing.
Your comments are greatly appreciated.
Note that links are informational only, the information compiled by others
would not be used without prior express approval.
************************************
Physically a Lego set can consist of elements, instructions, packaging and
possibly other materials. Logically a set can be described by type of model,
a set number or
a set name
***Set Information table
set_info table
set_info_key int, un, PK Key
set_id varchar (10) Set ID.
theme_code int, un Numeric theme/Subtheme Code
model_type int, un Numeric model type code (for the main model)
alt_type int, un Numeric model type code (for the
alternate model)
rel_date date Release date
dis_date date discontinue date
rel_note text Notes about the release dates
elem_count int, un Element count
count_type char Type of count
count_src char Source of element count
***Set Number Table
Lego identifies most of their sets with a numeric set number. However these
numbers are not unique:
1. The same set may have different numbers in different markets.
2. Different versions of the same set may have different numbers
3. Different sets may have the same set number (reuse of a number)
set_num table
This table links the set_id to 1 or more set numbers using set_id and set_num.
set_num_key int, un, PK Key
set_id varchar (10) Set ID
set_num varchar (10) Set number
***Set Name Table
A Lego set may have different names in different markets. In a given market
this name can also change from year to year. Lego markets its products in
languages
using standard latin characters, but also in languages using non-latin
characters, such as Arabic, Turkish and Japanese.
The International Stardards Organization specifies the code value of a
character in the ISO standard ISO/IEC 10646-1:2000 "Information
Technology--Universal
Multiple-Octet Coded Character Set (UCS)--Part 1: Architecture and Basic
Multilingual Plane".
http://anubis.dkuug.dk/JTC1/SC2/WG2/
To implement this standard a subset of ISO 10646-1, the Unicode standard,
version 3.0 is used:
http://www.unicode.org/
Unicode 3.0 uses 16 bit codes (an octet pair) to specify characters allowing
the use of non-latin characters. The UTF-8 form preserves the exsisting
ASCII codes.
Note however that not all RDMS (read: mySQL) currently support unicode
characters.
set_name table
The table uses the set_num as the primary identifier and allows a set number
to be associated with a specific name. The set_name and iso2_code are used to
associate a name with a country. The name_year specifies the year a name
occurs and allows for multiple names from a single country. The name_src is
used to
indicate where the name is from (e.g box, catalog, etc)
set_name_key int, un, PK key
set_num varchar (10) Set number
set_name varchar (100) Lego set name in the specified
language (UTF-8)
iso3166_2code char (2) Country designation for set name
name_year int, un Year of name from a given country
name_src char Source for set name
***Media table
The media in a set obviously includes the instruction manual or manuals, but
may also include registration cards, catalogs, posters, CD-ROMS or VHS video
tapes.
The table is designed primarily to describe an instruction book but can
accomidate other media as well.
media table
media_type, media_num and media_pages identify the type of media and the
Lego part number. The iso216_code identifies the size. If the size is not
an ISO standard
size the exact dimensions can be decribed by media_width, media_height and
media_dim_unit.
media_key int, un, PK key
set_id varchar (10) Set ID
media_type char Type of media
media_num char Lego part number
media_pages int, un Number of pages
iso216_code char (2) ISO 216 size code
media_width int, un Width for non-ISO 216 sizes
media_height int, un Height for non-ISO 216 sizes
media_dim_unit char Unit of measure for specified dimensions
media_note text Notes
***Set Inventory table
Inventories have been presented in several different ways. Currently the
most complete lists of inventories is found on the Lugnet Set Guide's
Repository of Parts
Inventories, maintained by Todd Lehman and Suzanne Rich:
http://www.lugnet.com/inv/
or the Inventory Database on Peeron.com, maintained by Dan and Jen Boger:
http://www.peeron.com/inv
inventory table
inv_key int, un, PK key
set_id varchar (10) Set ID
elem_id varchar (10) Element ID
elem_count int, un Numeric element count
color_code int, un Ldraw numeric3 code
inv_note text Notes
***Element Information Table
elem_info
elem_info_key int. un, PK Key
elem_id varchar (10) Element ID
elem_length int, un element length
elem_width int, un element width
elem_height int, un element height
elem_dim_unit char Unit of measure for specified dimensions
***Element Number Table
Lego identifies most of their elements with a numeric element number.
However these numbers are not unique and an element may consist of several
numbered
pieces
elem_num table
This table links the elem_id to 1 or more element numbers using elem_id and
elem_num.
elem_num_key int, un, PK Key
elem_id varchar (10) Element ID
elem_num varchar (10) Element number
***Element Name Table
Lego gives elements an official Danish name, although this name is not
typically available to the public. In general the official names do not
survive translation from
Danish very well and are not particularly descriptive. Also the same
official name is often used for multiple elements.
Common elements (regular bricks, plates, tiles and beams) have generally
agreed upon names but after that there is wide variation and little
agreement in naming. For
most elements the Lugnet Partsref, maintained by Steve Bliss, serves as a
good guide to names:
http://guide.lugnet.com/partsref/
For Technic elements, the Technica Element Registry, maintained by Jim
Hughes, is useful:
http://w3.one.net/~hughesj/technica/registry.html
The element name table can accomidate as many names for an element as you want.
elem_name
elem_name_key int, un, PK key
elem_id varchar (10) Element ID
elem_name varchar (100) Element name
name_src char Source for element name
***Element Color Table
This table lists the first known occurance of a given element in any color.
It serves as a proof of exsistance rather than a detailed review of color
availability. This
information can also be generated from the set inventories but a complete
list of inventories is not likely to occur any time soon.
elem_color
elem_color_key int, un, key Key
elem_id varchar (10) Element ID
color_code int, un Ldraw numeric3 code
set_id varchar (10) Set ID
rel_date date Release date
***Color Table
Lego currently uses a special formulation of Bayer Novodur ABS for most of
its elements. Novodur is supplied as colorless granules to which a powdered
dye (a
lake) is added. Very small amounts of added dye will result in transparent
colors and more dye will result in opaque colors. Lego has gone well beyond
the primary
colors used in early elements and now produces elements in a wide range of
opaque, transparent and metallic colors.
Most previous work on describing color was done as part of the Ldraw
project, which maintains a list of colors and their associated values:
http://www.ldraw.org/memorial/archive/FAQ/#question-27
Currently the only attempt to organize element colors is the color guide
maintained by Suzanne Rich:
http://www.baseplate.com/colors/
color table
The table uses the Ldraw numeric code and Ldraw name to identify colors.
The RGB triplets are supplied to provide a means of generating color swatches.
color_key int, un, PK key
color_code int, un Ldraw numeric3 code
color_rgb char (6) RGB hex triplet
color_name char Ldraw color name in English
***Theme Table
Since at least the 60's Lego has marketed specialized product ranges, but
the concept of themes became more apparent in the late 70's, when Lego
released space
sets as part of their Legoland theme. Today all sets are marketed as
specific themes.
Creating a theme taxonomy is not trivial, particularly with older sets. The
most complete current list is the Lugnet guide's Category Tree maintained by
Todd
Lehman and Suzanne Rich:
http://www.lugnet.com/pause/search/browse.cgi
theme table
The table uses theme_code as a numeric identifier of a theme or subtheme.
Theme_parent is used to identify a parent/child and allows a heirarchical
relationship
among the themes/subthemes.
theme_key int, un, PK key
theme_code int, un numeric code for theme/subtheme
theme_parent int, un numeric code for parent theme/subtheme
theme_name char Theme/Subtheme name in English
theme_note text Notes
***Model Type table
The type of model is seldom accurately described by the official set name,
and alternate models are not described at all so a list of model types is
useful to provide an
useful generic description of the set.
model_type table
The table uses model_code as a numeric identifier of a model type.
Model_parent is used to identify a parent/child and allows a heirarchical
relationship among the
model types.
model_key int, un, PK key
model_code int, un numeric code for model type
model_parent int, un numeric code for parent model type
model_name char model name in English
model_note text Notes
***ISO_3166 table
The United Nations Statistics Division maintains a list of country and
region codes for statistical use which is published as "Standard Country or
Area Codes for
Statistical Use, Revision 4":
http://www.un.org/Depts/unsd/methods/m49.htm
The UN list is used by the International Standardization Organization as the
basis of the ISO standard ISO 3166-1 "Codes for the representation of names of
countries and their subdivisions - Part 1: Country codes" Which is
maintained by the ISO 3166 Maintenance Agency (ISO 3166/MA):
http://www.din.de/gremien/nas/nabd/iso3166ma/
The ISO 3166-1 list is used by IANA to establish country code top-level
domains for the internet:
http://www.iana.org/cctld/cctld.htm
iso_3166 table
The table uses the ISO-3166-1 alpha2 and alpha3 codes and the short English
country names as well as the UN numeric3 code. The alpha2 or 3 code is used to
designate the country of origin of any Lego set name. The English country
name is used as an identifier for the alpha codes, particularly for some of
the more
obscure countries. The UN numeric3 code is primarily for completeness and is
not currently used.
iso3166_key int, un, PK key
un_3code int, un UN numeric3 code
iso3166_2code char (2) ISO_3166 alpha2 code
iso3166_3code char (3) ISO_3166 alpha3 code
iso3166_name char Short name in English
***ISO 216 Table
The International Stardards Organization specifies paper size in the ISO
standard ISO 216 "Writing paper and certain classes of printed matter --
Trimmed sizes -- A
and B series". A series paper has a width-to-height ratio of the sqrt(2)
(1.4142). When cut parallel to the longer side the resulting paper still has
the same
width-to-height ratio.
The standard is not available on the internet but a good review is found here:
http://www.cl.cam.ac.uk/~mgk25/iso-paper.html
The US is the only major industrial country that still uses it's own paper
size format.
iso_216 table
The table uses is ISO 216 code as an identifier for a given width and
height, iso216_width and iso216_length.
iso216_key int, un, PK key
iso216_code varchar (3) ISO_216 alpha2 size code
iso216_width int, un paper length in mm
iso216_height int, un paper len in mm
**********************************************************************
Thanks
--Jim
|
|
1 Message in This Thread:
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
