Subject:
|
Re: A Technic set DB outline
|
Newsgroups:
|
lugnet.admin.database
|
Date:
|
Mon, 30 Nov 1998 04:35:59 GMT
|
Reply-To:
|
lpien@ctp.iwantnospam.com[SayNoToSpam]
|
Viewed:
|
808 times
|
| |
|
|
First off, let me state that were I designing this from scratch I'd do
an object model not a data model but...
Jim Hughes wrote:
> >
> > Theme table seems empty.
>
> It is. I am still trying to figure out how to put themes and subthemes
> into a table.
You need a hierarchy table. Model it like this (not legit SQL)
Table Theme
ThemeID integer PRIMARY KEY NOT NIL
ThemeType Code /* maps to code table with values such as "system",
"theme", "subtheme" etc... */ NOT NIL
Name Varchar80
Description Varchar255
ParentTheme (->ThemeID) integer
Now you have one theme that has a nil ParentTheme and type code of
"root"
Each SYSTEM theme the has root as parent
Each theme like town has a SYSTEM theme as parent
Each subtheme like Race has a theme as parent, and so forth.
>
> >
> > MinifigTable. Will this just index into images. For Technic that's fine,
> > but for real minifigs it may be better to model the parts separately, so
> > a minifig is comprised of a torso, arms, legs, hands, head, hairpiece,
> > etc.
I forgot to make a key point which is that by modeling the minifig from
its constituent parts you're more compactly representing the
differences, as well as allowing searches such as "what sets can I get
black hands from"
> > Set Table.
> > What do the indexterms in the set table do?
>
> The index terms are my idea of terms like, helicopter, auto chassis, etc.
> The lego names by themselves seldom indicate what the model exactly is. I
> envisioned doing searches like show me all of the forklift models between 1977
> and 1885, e.g.
Ah. OK. Again break this out to a separate table of index terms. Create
an association table
Set-Index Table
SetID integer PRIMARY KEY NOT NIL
IndexID integer PRIMARY KEY NOT NIL
ModelID Integer PRIMARY KEY NOT NIL /* is this the primary or an
alternate model */
CorrelationFactor Float /* HOW 'helicopterish' is this model, as a
percentage? */
and then associate to index terms such as helicopter etc. assigning each
an Index ID.
If the set (say rebel wrecker) has three models, one primary and two
alternate, one of which is a 4x4 tow truck, that model has a set-index
table entry with 100% correlation with indexID for index "tow truck"
and 80% correlation with "four-wheeler". And so forth for each model. A
model that is a pickup with a trailer and a sub on it would correlate to
both pcikup and sub but maybe not at 100%.
> Actually this is currently my biggest problem, how do you model the data for
> 1. A set with different numbers and different names
> 2. a set with the same number and different names
> 3. a set with different numbers and the same name
> etc.
Again, association tables are your friend here. By now the pattern
should be emerging. You've denormalized your tables too much for a
logical design. Stay logical and fold the stuff in later to optimize
performance.
>
> > Are the intro and disco dates by "model year"? How do you capture mid
> > year intros? For a set stated to be available Jun 99, do you put in May
> > because that's when it was orderable from S@H??
>
> I am considering just year, not month/year, because I dont have month/year
> information on most early Technic stuff, and sets tend to have different
> month intoductions depending on where you are.
In a logical design, go for full data capture. Compromise your
implementation, or use defaults. Some mid year intros are known. Don't
preclude their capture because some of your datea is dirty. Scrub the
dirty data instead.
> > Both the element and minifigs need to be done with intermediate
> > associations.
> > Color is totally derivable from elements if modeled correctly, unless
> > this is noting the "sense" of the model color.
>
> The elements table is designed to include only the most specialized of
> the specialized elements (like motors, RCX's, pneumatic elements) and color is
> a very general indicator (e.g color; red/black, gray) of the model.
I'd model every part included in the set. Maybe not at first but get the
schema right and maybe at first it only has a few special elements. Over
time others can fill in the details.
> again, thanks for your comments
you're too welcome. Anyone can suggest, you're signing up for the hard
part, the implementation.
--
Larry Pieniazek http://my.voyager.net/lar
For me: No voyager e-mail please. All snail-mail to Ada, please.
- Posting Binaries to RTL causes flamage... Don't do it, please.
- Stick to the facts when posting about others, please.
- This is a family newsgroup, thanks.
|
|
Message has 1 Reply:
 | | Re: A Technic set DB outline
|
| (...) In case accuracy WRT TLG is important here, the actual hierarchy looks like the following (I may never forgive Todd for his invention of the term "subtheme" :-P) Product Programme +- Product Line +- Product Theme "Race" is a theme in the "Town (...) (26 years ago, 1-Dec-98, to lugnet.admin.database)
|
Message is in Reply To:
| | Re: A Technic set DB outline
|
| Larry: Thanks for your comments, this is exactly the type of discussion I hoped posting this would generate: (...) Yes. (...) It is. I am still trying to figure out how to put themes and subthemes into a table. (...) This makes alot more sense than (...) (26 years ago, 30-Nov-98, to lugnet.admin.database)
|
9 Messages in This Thread:
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
This Message and its Replies on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
Active threads in Database
|
|
|
|
|
|
