Re: Database model - confusion

[Marc M]

Roland, Thanks again for your generous post. I have to admit, I'm kinda spinning from that last post.
I agree that this may be more complex than it realistically needs to be. This is something that I'm going to have to really consider before moving forward. I want it to be as simple as possible while still being robust and expandable for future growth. Perhaps I do have too many dependencies.

I've been laying things out and it seems like it might be taking shape. Currently, my solution is this:

----------------------

product_types table:
pr_types_id (pk)

product_file_type table:
product_filetype_id (pk)
pr_types_id (fk) --> decides available file type by product type

categories table:
categories_id (pk)
pr_types_id (fk) --> decides category type by product type

products table:
product_id (pk)
pr_types_id (fk) --> defines product type in product table

product_files table: has a many to 1 relationship with products
product_id (fk) - identifies parent product (a product can have many files)
product_filetype_id (fk) defines the type of product file for the product

----------------------------

same tables as above, but this is a test layout - don't know if it will show right. If it's all jumbled, ignore this section...

product_types table:
-- -- -- pr_types_id (pk)
| | |
| | | product_file_type table:
| | | | product_filetype_id (pk)
| | | +-pr_types_id (fk) --> decides available file type by product type
| | |
| | | categories table:
| | | categories_id (pk)
| | +-pr_types_id (fk) --> decides category type by product type
| |
| | products table:
| | product_id (pk)
| +-pr_types_id (fk) --> defines product type in product table
|
| product_files table: has a many to 1 relationship with products
| product_id (fk) - identifies parent product (a product can have many files)
+-product_filetype_id (fk) defines the type of product file for the product

note: what this doesn't show is that there is a connection between products and categories AND between products and product_files.

----------------------

------------------------
Inner space - what a trip.