I'm looking for a little "achitectural" advice.

[mOrloff]

I will be storing inventory in a DB.
Each of the product types will have some unique attributes.

Lets use foods as an example (mmm, because I like food sooo much).Let's say that we are selling food, and each specific type gets a Part Number.
A Bosc pear will have its own part number, as would a Comice pear, as would an Armenian Melon.
A Porterhouse Steak would have its own part number, as would a Tri-tip, as would a Leg-of-Lamb.
Rye flour would have its own PN, as would red-Wheat flour, as would white-Wheat flour.

Now, in addition to just being able to search for a PN, we want to have a way for items to be searched by parameter.
If someone selected "Produce", they would then be able to select between "Fruit/Vegetable", then "Pear/Apple/Melon/Stone-fruit", then etc...
If they Selected "Meat", they could then drill down to "Beef/Goat/Lamb", then "Steak/Roast", then etc ...
Same type of options for Grains.

As you can see, there can't be a "vegetable" attribute for meats or grains. That attribute is unique to produce.
(Please keep in mind that this is just a simplified example.)

Now, would it be most practical to split up each of the inventory types into it's own table? While this would allow that table to have an efficient use of columns, this could make searches by PN rather cumbersome (having to run the query on each and every table individually).
Whereas, if we combine them into one table, there would be tons of columns (and most of them would be empty), but the search by PN would be more efficient.

I'm looking for advice and suggestions.

Also, are there already tools/structures out there for this?

Does anybody have any creative ideas?

Thanks-a-bunch,
~ Mo

[bazz]

I recommend you put it all into one table. there is nothing wrong really with having many null values - that is, I suppose, what null is for.

Read the MySQL docs and of course the threads in this forum for loads of help.

Tools for this? phpMyAdmin or heidiSQL are the two I know of.

Don't forget that the barcode is unique to each product so it may be useful to use the numerical part as your PK. (I don't know for sure coz I haven't done that type of db yet). (Oops, I forgot - that was just an example ).

bazz

[Old Pedant]

Absolutely agree w/ Bazz! No way you should use separate tables.

Now...

What about if your products fit into multiple categories??

Let's see... okay, a frozen meal with meat, vegetable, and fruit cobbler.

Ignoring the fact that you probably would have a "frozen meal" category, if you wanted to be able to find that item via meat *OR* vegetable *OR* fruit, then you need a many-to-many table to link each product to multiple categories.

But from what you have described so far, that's about as complex as you need to get.

[brad211987]

For ease of searching and programming, I would use multiple tables. Here is a basic example:

Parts table: stores all info about your parts, keyed by the ID

types table: stores types, probably only 2 or 3 columns, such as Type ID, Type Name, and Type Description.

parts_types mapping table: this just maps parts to types in a many to many relationship, only columns needed are part id and type id.

This allows you to pull parts for a type with a very simple query such as:

select * from parts_types where type_id = <id of type for 'meat'>
instead of having to do a complex where clause. Also if you put it all in one table, every time you need to add a new attribute, you need to modify your table schema, and then try and retrofit the data.

[bazz]

Just to clarify my earlier post... whe I said put it into one table, I was responding to this question


Now, would it be most practical to split up each of the inventory types into it's own table


what you should do is along the lines of what brad said: have a table for produce and another for 'categories' and a third which is the mapping table and which stores the PK of each of the first two tables.

Make sure you use innodb and foreign keys to maintain RI as it is much more efficient than relying on code to do it.

As a side issue, but no less important, do not use the star selector because:

1. you will retrieve all the table data even if you don't want it
2. as you develop your db, if you add more columns, to any table, you will be very likely to break existing queries and so your database is then not scaleable.

Just select the columns you want when qurtying and when inserting, use the set clause to make sure that the data for input goes into only the columns you want it to. as you add more columns, this typoe of insert will continue to work regardless.

bazz

[mOrloff]

Thanks to you all.

I just got back from the weekend, and am very happy to see some good advice.
Each and every post had something I could use.

Once again, thanks-a-bunch.
~ Mo

[mOrloff]

OK, a couple questions remain.
Regarding:...Make sure you use innodb and foreign keys to maintain RI as it is much more efficient than relying on code to do it ...
I looked into InnoDB, and that seems pretty cool. I'm not quite sure how to utilize it yet, but it sounds helpful.

Now, as for foreign keys, I'm at least familiar with what these are, but are you advising on the use of foreign keys in
each table as well as the relationships table, or ... something else?

As for RI, that's completely blowing past me. I'm not familiar with that acronym. Please disambiguate :).

~ Mo

[bazz]

OK, sorry for the confusion.

I meant use innodb instead of MyIsam so that foreign keys are supported. MyIsam works quicker than innodb but doesn't support FK's so, if you would be better with them, innodb makes more sense.

foreign keys - how to explain....
say you have a product table as below:


create table products
( id int not null auto_increment primary key
, product_name varchar (99) not null
) engine=innodb default charset=latin1;


and then you have another table which has product sizes


create table product_sizes
( id not null auto_increment primary key
, size varchar (24) not null
, size_measurement varchar (12) /* eg kg or lbs */
, product_id int
, constraint size_product_fk /* a label to facilitate alterations */
foreign key (product_id)
references products(id) on delete cascade
) engine=innodb default charset=latin1;


(sample charset used)

OK, do you see how the product_id in the sizes table is related and constrained? basically, if you remove a product from the products table, say, when it is discontinued/obsolete, all the details of sizes will be removed in the product_sizes table, where the product _id is the same as the one being deleted.

If you read up on foreign keys now in the docs, it should a make more sense.

As for RI, it just means referential integrity which you can google. Basically it means that there will be no redundant data in the db and there is much less likelihood of old stuff that should be gonie, being there to be confused with other products.

I have stored its relevance in my head as being - that's the way to do it best.

hth

bazz


Further to that, if you had another table say 'labels'

create table labels
( id int not null auto_increment primary key
, label_type varchar(32)
) engine.....
eg paper, metal, plastic

and you needed to relate the products (many) with the available labels (many), you need a many to many table then you could relate the products to the labels in a 'many to many' table like this

create table products_to_labels
( product_id int not null
, label varchar(32) not null
, primary key ( product_id , label )
, index ( label , product_id )
) engine...




the contents of the last table could be like this

product_id | label |
| 1 | plastic |
| 1 | metal |
| 2 | plastic |
| 3 | metal |
etc


The labels table also becomes useful in your cms system for inputting data via a select dropdown box. It means you can only input data which is in that table and of course, if you add constraints to that many to many table, the data will be removed from it by MySQL, automatically, if you delete a product in the products table or you remove a label from the labels table. so choose your constraints carefully . restrict(default), delete, and about three others, which I can't recall.
hth

bazz

[mOrloff]

Wow! That constrained stuff is pretty cool.
Great explanation. Thanks.

You've provided even more fodder for learning.
Back to Google I go.

Again, thanks-a-bunch.

[mOrloff]

Sooo ... with InnoDB, I'm yet to find a quick "big picture" tutorial.
Do you only have to declare innodb as the engine when you instantiate the tables, and then you're off to the races, or is there something more like a special query syntax or ???

[bazz]

Just declare it as your table engine and your at the races. :D

No special query syntax but they are shorter and more efficient because what you would have to include in them (if your tables were MyISAM), is handled behind the scenes by MySQL.

say you want to delete something from one table: instead of telling the query to delete from this table where this = that and then delete from second tbale where this=that and delete from third table where this = that you can delete from just one and MySQL does the rest provided you have your constraints set up correctly.

bazz

[mOrloff]

...instead of telling the query to delete from this table ... and then delete from second tbale ... and delete from third table ... you can delete from just one and MySQL does the rest ...
VERY nice!!
That is so slick.

Man, I love learning new things!

~ Mo

BTW: thanks for the further info in the edit

[mOrloff]

OK, here's the question I have now.
If I wanted to have categories and sub-categories in parent.child relationships, what's the best way to incorporate that?
(For example, the "meat" category could have a "beef" child and also a "lamb" child.)

I've got a few ideas (and a couple of them may be a bit hair-brained):)
- One idea is to have ALL the categories in one table, and have a field for "is_parent", or something like that, and also have a "parent_category" field.
It would be either/or for those two fields (either is_parent="Y", or parent_category={category_name}).
Table: Categories
| category_id | category_name | is_parent | parent_category | category_description |
In this case, is there a way to constrain the "parent_category" to "category_name"?

- Another idea is to have categories and subcategories each in their own table. Then have a relationships table relating subcategories to their prospective (parent)categories.
Table: category_subcategories
| category_id | subcategory_id |
If I went this way, to make my queries easier (by having oine table which all the others can be directly joined to), should I include both on the many-to-many relationships table for the products & categories.
Table: products_categories_subcategories
| include product_id | category_id | subcategory_id |?

- There's more where those came from, but these seem like the 2 most-likely candidates that I came up with.
Are there better models for this?

I'm holding my breath in anticipation :)
~ Mo

[bazz]

You can use one table for these relationships. Hang on a bit and I'll find you the tutorial that explains it well - much better than I could.

well this isn't the page I was looking for but it is by the same guy.
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid518422,00.html

bazz

[Old Pedant]

You *can* use one table, and if you aren't sure if a given category will have any subcategories, that could well be best way to go.

But if all categories have subcategories, and all products fit into one or more subcategories, then I'd go with separate category/subcat tables. It's cleaner and easier to manage and you don't end up doing self-joins. (Not that self-joins are necessarily a bad thing...just that it's easy to make mistakes with them and harder to read the queries involving them, if you aren't careful.)

I'm a great believer in K.I.S.S. Which we shall choose to read as "Keep It Stupidly Simple".

[mOrloff]

Bazz & OldPendant, thanks for your pointers and advice (and the link).
It looks like it's turning out like everything else in life ... I actually have to make a decision.

I'll seriously analyze my probable and possible future scenarios, then flip a coin ;)

Again, thank you.
I feel much more capable to move forward now.

~ Mo