ToyStore db design check

[knight07]

Hello all:

I'm exteremly new to this wonderful forum and to filemaker itself. My question is regarding toystore system that I'm trying to make (and am not sure if this is the right place to post this!). This system is to store all items info and track all transactions (mainly purchases and returned items).

I created 6 tables:

[color:green]toys(toy_ID (pk), toy_description, category, original_price, supplier_id (fk)).

[color:green]suppliers(supplier_id(pk), supplier_name, supplier_telephone, supplier_address).

[color:green]jncttbl (supplier_id(fk), jncttbl_id(pk), manufacturer_id(fk)).

[color:green]manufacturer(manufacturerID(pk), manufacturer_name, country_id(fk)),

[color:green]countries(countryId(pk), country_name).

besides I might need to add one or more tables to track the transactions.

So my question here does this look right? Am I doing it correct?

[Ender]

Greetings, Knight!

I think you're missing a couple things. There doesn't seem to be an association between Toy and Manufacturer, which could be handy. I'd suggest linking an occurrence of the Manufacturer table to the Toy table via ManufacturerID, and add a separate Toy-Supplier join table for tracking who supplies toys over time.

If you're going to track purchases and returns, then you'll need some structure to account for them. Probably a Customer table, a Transaction table, and Transaction LineItem table (Transaction-Toy join). You should be able to handle both purchases and returns in such a structure. Though you may wish to track Payments separately (sometimes Payments are made for multiple invoices, or multiple payments are made on a single invoice).

If prices of Toys change over time, it may be useful to track the Prices in a separate table related to Toy. You would then have a history of Prices over time, and could even set up Price changes ahead of time and use a date range relationship to pull in the right price for the transaction date.

[knight07]

Thanx Ender, . This is a great help and very encouraging and cheerful beginning for me in here. You're right in the first point, I messed up by not linking the toys and manufacturers tables;will correct it.

Regarding the second point, I'm so glad that you explained this to me. I've already made a table for the transactions but was waiting until I work on the first part and test. I will certainly need your post when I work on the transactions part.

Thanx again. :