vernick@sbcs.sunysb.edu (Michael Vernick) (10/11/90)
I have designed a small inventory database system but have been having some performance problems based on my design. I'd like some comments about my high level design and my implementation. Most of my work in Database systems is Theoretical not practical. We start with only 2 entities, Owners and Components, and 1 relationship Owns where Owners Own/Owned Components. An Owner has attributes name, place, deptartment, etc... A Component has attributes manufacturer, name, model, serial #, size, class (software/hardware), subclass (database/monitor). In this model there is a lot of redundency. Many components may have the same manufacturer, name and model. Because of the redundency we may also have update anomalies. If a manufacturer changes its name we must fix it in every component. There are also insertion anomalies, we cannot enter a Product into the database unless it is part of a component. I have thus decomposed Components into 2 relations, Components and Products with a many-one relationship from Components to Products. Also, many Products may have the same class and subclass. I have thus decomposed Products into Products and Classes with a many-one relationship from Products to Classes. By performing these decompositions we remove the redundency and anomolies. Now we have 5 relations implemented as follows: Owners: Owner Id(Key), Name, Dept, Address, ...... Owns: Owner Id(Key), Component Id(Key), Installed Date, Removed Date, Owns is a history of Who owns/owned what components. Component: Component Id(Key), Serial #, Leased, Product Id.... Products: Product Id(Key), Manufacturer, name, model, Class Id... Class: Class Id(Key), Class Name, Sub Class Name We currently have about 400 Owners, 1500 components, 400 products and 60 classes. My problem now occurs when I want to extract information. If I want to find out all the information about a Component, I must perform a join between Components, Products and Classes. This takes much longer than just looking up a Component tuple that includes all the information. If I just have one Component I am asking about it doesn't take too long. If I want information about all components (for a report) I have been running out of disk space (only had 2 meg free). (I am using the Paradox DBMS on a Compaq 386 w/ 8 Meg of memory). No matter what, I still want to keep the Products relation and Classes relation. These are lists of the allowable products and the allowable classes. For execution reasons should I have the Components tuple contain all of the product information, rather than have pointers (via keys) to the other relations. Theoretically, this is not the best design. Practically it seems to be. Comments? Alternatives? Criticisms? Suggestions? All are welcome. Thanks. -- Mike Vernick vernick@sbcs.sunysb.edu