Database design for a small computer shop, looking for suggestions

As an exercise for learning some database design techniques, I'm designing a database for a fictional small computer shop. The idea is that the shop gets some components from multiple suppliers, assembles them into computer systems and sells them to customers.

Here's an image of the model I've come up with so far: Image

Some explanations:

The central entity of this model is an Item. It represents a single item the shop owns or has sold, for instance one precise i7 920 processor. This Item belongs to a Product (in this case "Intel i7 920"), which belongs to a Category ("Processors").

These Items enter the shop via an Order. When components are ordered from a Supplier, a new entry in the Orders table is created. For each Item that is ordered, a new entry in the Items table is created, with its corresponding entry in OrderItem (I couldn't come up with a better name for this table...).

An OrderItem entry basically contains what the shop received on the invoice from the supplier: the price that item cost, the same description, etc. The order field determines in which order the items appear if the Order must be displayed or printed.

On the other side, when a Customers buys something, an Invoice is created. Each Item that they buy, an InvoiceItem is created. It basically works the same way as OrderItem. The price field is the price the Customer will pay (let's assume the prices are not per-product, but customized for each Invoice. I couldn't find an elegant way to have per-product price and be able to track the price changes). If description is NULL, the description of the corresponding Product appears on the Invoice, else, a custom description for that Invoice can be entered.

Now the tricky part is the Assemblies table. If the shop sells a PC system called "PC 1", there will be a "PC 1" entry in Products, belonging to the "PCs" category for example. Every time a "PC 1" system is assembled, a new Item is created (with the productID of "PC 1") and for each component of this system, an entry is added to Assemblies: assemblyID will be the itemID of the new Item, componentID is the itemID of the component.

I just thought of another way of managing this without the开发者_如何学Python Assemblies table: the shop could "sell" the components for 0 $ (well actually 0 CHF) to a fictional Customer and "buy" the assembled computer from a fictional supplier (again, for free). This seems easier to do, as the components would disappear from the stock, but would there be an easy way to link the "sold" components to the the "bought" computer?

Now, I'm pretty happy with what I've learned form that. But there are certainly some errors or some better ways to represent things I couldn't think of. I'm happy to receive any suggestions and criticism. Thanks in advance!

PS: Sorry if the text's a bit long... Also, sorry for some cases of bad English (it's not my main language (that would also explain some poorly choses field/table names (for which I would be more than happy to get suggestions)))

The process you are describing in the creation of a PC from individual parts is a Bill of Materials, here is a website with a lot of information about databases and they actually have data model examples, including bill of materials.

The process you are working on at this time is called Database Normalization, the act of making database scalable and faster when it comes to query processing times.

Right off the bat I can see Remarks in the Customers table should be given its own table called Remarks which gets tied to your Customer table via the CustomerID. This way the customer can leave unlimited remarks.

Same thing with Invoices, you might also want to setup a Payments table to control all the Payments made by all customers, tying both these tables together with PaymentIDs and InvoiceIDs respectively.

Hope this helps.