If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
? Plan For "Unified" Home Inventory/Tracking Database
I want to created a "unified" home Inventory/Tracking system using Access
2003, and I wonder if anyone has any comments on my idea, and if it can be done without breaking all the design rules. I want to make a system that has a common front end regardless of if the item is a CD, DVD, tool, slides in a slide tray, files stored on my computer, etc. These items are all similar in that they have a name(CD title), they have a physical characteristic(LP record), someone made it(actor/artist), they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels), and they might have subparts(track name). The basic difference is the headings that would appear on queries and reports. My initial thought is to create a "base" table that contains the intrinsic name of the item (primary key), basic common information columns, and column(s) containing indicators (you can tell I was a programmer) describing the basic phyical characteristic(s) if the item, such as if it is a CD or power tool, where it is physically located, etc. This table would then be linked to a table (and possibly subtables) which would contain enough columns to describe the "details" of the item. For some items, some columns or subtables wouldn't be used. This way I could create more generic queries and reports by using VBA and the values of the indicator columns to dynamically change the headings, etc. to match the appropriate item. Some storage might be wasted for columns that are not used by some items, but I would rather have a unified system than one that uses the least disk storage. Any basic thoughts on my plan? Lastly, I just read "Microsoft Office Access 2003 Inside Out" by John L. Viescas, and I'm wondering why I would even consider making a "local" .mdf database instead of the "upgradable" .adp project type. I have MSDE running on my system, and the only reason I can possibly think for using .mdf databases is for possible performance issues. Am I missing something here? -- Carl |
#2
|
|||
|
|||
? Plan For "Unified" Home Inventory/Tracking Database
"BitPusher" wrote in message news I want to created a "unified" home Inventory/Tracking system using Access 2003, and I wonder if anyone has any comments on my idea, and if it can be done without breaking all the design rules. I want to make a system that has a common front end regardless of if the item is a CD, DVD, tool, slides in a slide tray, files stored on my computer, etc. These items are all similar in that they have a name(CD title), they have a physical characteristic(LP record), someone made it(actor/artist), they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels), and they might have subparts(track name). The basic difference is the headings that would appear on queries and reports. My initial thought is to create a "base" table that contains the intrinsic name of the item (primary key), basic common information columns, and column(s) containing indicators (you can tell I was a programmer) describing the basic phyical characteristic(s) if the item, such as if it is a CD or power tool, where it is physically located, etc. This table would then be linked to a table (and possibly subtables) which would contain enough columns to describe the "details" of the item. For some items, some columns or subtables wouldn't be used. This way I could create more generic queries and reports by using VBA and the values of the indicator columns to dynamically change the headings, etc. to match the appropriate item. Some storage might be wasted for columns that are not used by some items, but I would rather have a unified system than one that uses the least disk storage. Whenever you have 1...n things that you want to show dynamically on a form or a report, you are talking rows, not columns. So even though the 1-n things are bits of data about various things in your database, you need to find a way to make those things rows. Probably the easiest would be something like this: ThingTypes (Defines types of things that could be described in your db) ThingTypeId ThingTypeDesc ThingTypeProperties (Lists properties a given thing type could have) PropertyID ThingTypeId PropertyDesc PropertyDatatype PropertyFieldlength Things (things that are actually being described) ThingID ThingDesc ThingTypeID ThingProperties (actual properties of a given thing) ThingID PropertyID PropertyValue The issue with this is that Access forms don't lend themselves to this type of structure, so you'll need to use a Left join of ThingType with ThingtypeProperties to allow you to "pull" a list of properties to fill out about a particular thing of that type. HTH; Amy |
#3
|
|||
|
|||
? Plan For "Unified" Home Inventory/Tracking Database
Thanks Amy for the feedback.
I'm now changing my original idea to incorporate your suggestions. First I'm going to set up something very basic and simple to make sure I can get it to work using MSDE. I don't have much documentation for SQL Server, so I might have to head to my local Border's or Barnes and Noble to get some books about it and its management. Thanks again, -- Carl "Amy Blankenship" wrote: "BitPusher" wrote in message news I want to created a "unified" home Inventory/Tracking system using Access 2003, and I wonder if anyone has any comments on my idea, and if it can be done without breaking all the design rules. I want to make a system that has a common front end regardless of if the item is a CD, DVD, tool, slides in a slide tray, files stored on my computer, etc. These items are all similar in that they have a name(CD title), they have a physical characteristic(LP record), someone made it(actor/artist), they are kept somewhere(in a slide tray, in my Zio Tek Media Carousels), and they might have subparts(track name). The basic difference is the headings that would appear on queries and reports. My initial thought is to create a "base" table that contains the intrinsic name of the item (primary key), basic common information columns, and column(s) containing indicators (you can tell I was a programmer) describing the basic phyical characteristic(s) if the item, such as if it is a CD or power tool, where it is physically located, etc. This table would then be linked to a table (and possibly subtables) which would contain enough columns to describe the "details" of the item. For some items, some columns or subtables wouldn't be used. This way I could create more generic queries and reports by using VBA and the values of the indicator columns to dynamically change the headings, etc. to match the appropriate item. Some storage might be wasted for columns that are not used by some items, but I would rather have a unified system than one that uses the least disk storage. Whenever you have 1...n things that you want to show dynamically on a form or a report, you are talking rows, not columns. So even though the 1-n things are bits of data about various things in your database, you need to find a way to make those things rows. Probably the easiest would be something like this: ThingTypes (Defines types of things that could be described in your db) ThingTypeId ThingTypeDesc ThingTypeProperties (Lists properties a given thing type could have) PropertyID ThingTypeId PropertyDesc PropertyDatatype PropertyFieldlength Things (things that are actually being described) ThingID ThingDesc ThingTypeID ThingProperties (actual properties of a given thing) ThingID PropertyID PropertyValue The issue with this is that Access forms don't lend themselves to this type of structure, so you'll need to use a Left join of ThingType with ThingtypeProperties to allow you to "pull" a list of properties to fill out about a particular thing of that type. HTH; Amy |
Thread Tools | |
Display Modes | |
|
|