> > FOREIGN KEY (`cases_ID`)
>
> I don't see that field in `items`.
>
I originally named it so, but then I realised that "package" fits better, I forgotten to change it in the foreign key.
> > Example: “passive elements” group should
> contain “max voltage and resistivity” ,
> “microcontrolers”
>
> That makes a case for
> CREATE TABLE PassiveElements
> id ...,
> max_voltage ...,
> etc
> (But, then, you will have trouble getting to that
> table.)
Number of groups can change (create and delete) so this is not a good approach (I think so ;) )
> > CREATE TABLE IF NOT EXISTS `parameters` ( PRIMARY KEY (`ID`, `typeID`),
> > CREATE UNIQUE INDEX `ID_UNIQUE` ON `parameters` (`ID` ASC);
>
> Eh? A PK is a UNIQUE index. Is ID unique, if so,
> why are you declaring the combination (ID, typeID)
> unique?
Good question...
> > I can connect a ITEM to a MAGAZINE and set the number of them.
> > CREATE TABLE IF NOT EXISTS `itemsMagazinesLinks`
>
> Is this a many-to-many relationship between items
> and Magazines? If so, the PRIMARY KEY should be
> (items_ID, magazines_ID) (or vice versa). What
> you have will fail to prevent duplicate entries.
> And, once you have a PK like that, you don't need
> ID.
I will correct that
> > CREATE TABLE IF NOT EXISTS `itemsInSets` (
>
> Another many-to-many? Same suggestions.
>
> Or maybe not?
> > `quantity` DOUBLE NOT NULL,
> For example, 2 resistors are needed in set#1; 4
> resistors are needed in set#2?
>
> > CREATE TABLE IF NOT EXISTS `childrenSets` (
>
> Since MySQL has no hierarchical (recursive) SQL,
> you will need to deal with trees in code.
>
> Also, a simple hierarchy does not need a parent id
> and a child id. Instead it needs an ID (PRIMARY
> KEY) and a parent_ID (INDEX). The parent_ID JOINs
> to the ID to go up the tree. Looking up via
> parent_ID will find all the 'children'.
As I said before, I want to create tables in witch I can save a recipe.
for example: take 1k resistor OR 2k resistor and weld it together.
This OR is important for me, and I will get to it later.
> > create a resistor from wire
>
> That's not what I learned in EE courses.
http://en.wikipedia.org/wiki/Resistor#Wirewound ;)
> > Should I add a new column(s) to items table to
> say that this item is a set?
>
> Not if an `item` can be in more than one `set`.
some items can be in even all sets :)
> Maybe I missed something, but it seems like
> Magazines, Sets, and Groups are all the same thing
> --
> * A named collection, and
> * Contains Items or subtrees, and
> * Structured hierarchically
> Perhaps with some restrictions, such as a Magazine
> cannot contain a Magazine.
> I'm hinting that you may not need different tables
> for Mag, Set, Grp. Maybe just a `type`
> ENUM('mag', 'set', 'grp').
item table contains only information about items
magazine is a physical 'thing' I have 2 magazines, one in my home and second in work
set is only a recipe, containing information about what I can do with my items :)
> What are the queries?
> * Given an Item, what Mag/Set/Grp is it in?
> * Given a Mag/Set/Grp, what items are in it? And how many of each item?
* give quantity of elements in my magazine
* find elements with parameters fitting my needs (not only in mu magazine)
and the hardest part
* Give mi number of parts that I need to buy, to build/create X sets#N
and we must remember that sets can contain sets, that are created or not :) So it must tell my that I have elements for my set, and I have sets for my set and if I don't have sets to build my set it must add items for those sets to the list of things to buy... (quite complicated...)
> I don't have a clean answer for this query
> * Find which Mag/Set/Grp use resistor(s) between
> 10K and 100K ohms.
I often use other parts as a replacement, this is the replacement restrictions.
it should work like use resistor 47k or some resistor with resistance between 10k and 100k and this information should be in the sets table.
If you have trouble with understanding my problem please try to imagine a desk with a drawer full of ITEMS (a Magazine). When I want to create something (build a SET), I need to know if I have those elements (or replacements) in my drawer and where exactly are they. After creation I put the SET back to my drawer. So after this operation I don't have those items any more, but a set containing them instead.
But having tables like those I can't connect a set to a magazine...