Any Access Guru's like to comment?

[pauls]

Hi,

I'm reasonably new to database design (how many postings here start like that ) and I have two problems that I can't seem to find a solution to.

I am currently using MSAccess, however I will be putting in a MS SQL Server in the Summer as part of another project (not my choice, but a 3rd party product requires it) and so I will probably want to load up the various Access Databases to that at some point.

So my problems:-

I'm trying to write and inventtory package that will also handle my orders for equipment etc. etc.

1) Obviously the information I want to keep on an item depends upon what that item is. (For example a monitor doesn't have any memory and a printer doesn't usually boast a pentium!) So I figured that I could have one table that is the main inventory and then a sub-table for each type of equipment linked to this with a 1-1 relationship (but, and here's the catch, each row in the main table would be linked to ONLY ONE of several sub-tables). What I'm trying to achieve is an object-oriented style database with variable record types.

2) I want to include a notes field in many of my records. This would have to be a sub-table because I want to be able to date-stamp possibly multiple notes on a single record. Rather than set up a seperate sub-table for each master table, is it possible to have a shared notes table and in some way link not just to a record number, but also to a specific (variable) table?

I hope I'm making myself reasonably clear here, but if not, I'm sure someone will ask me to explain myself. I will understand if the answer is something like "Not with Access, but once you have SQLServer then what you do is...." although I'd like to get started now!

So, I throw myself on your collective mercy!

[CrazyCrane]

How about, instead of the layout you're considering, you had one product table for everything, but you stored their "properties" seperately?

Every product has some things in common - everything has a name, probably some kind of part code, a price etc.

The individual properties could be stored seperately, in a table which held the property "name" (e.g. resolution, amount of memory or whatever), the "value", and a link to whichever product it belongs to (so it's a 1 to many relationship).

This way, your notes problem becomes far simpler (you've not got multiple tables to link to); adding new types of product would be simpler as it's just new products, not creating a whole new table etc. (e.g. you suddenly need to start adding printers or something).

The data you need to store will dictate whether this is feasible or not, but if each product can easily be broken down into some common things, then a list of properties, it should work well.

Further amendments would of course be possible (e.g. you could have a master table of product types - monitors, hard drives etc. - and include a link to this table from the product table, so that displaying only one category etc. is easier). I'm sure there are plenty of additions you can think of, but the core of the database is always the most important, and generally speaking it'll be better to avoid having multiple tables to store essentially the same information.

Dunno if that helps at all, and I must point out I'm not an expert in DB design, but it's probably worth at least considering .

[pauls]

Thanks for the rapid response!

1) I see what you're getting at, but how would that work practically? This isn't a database for sales stock, but for an internal inventory. What I want to happen is when I add an item, I select (say) "Workstation" from a list and I'm immediately prompted for Processor, Memory, HDD etc. Selecting (say) "Monitor" would prompt for size, type, resolution etc. Certainly the common information (Location, Serial Number, Purchse date etc, etc.) would be held in what I consider to be the master inventory table, it's the "extra" stuff that's unique to a particular type of asset that is causing me the problems.

Thanks again for the idea though and I'll have a look at it in detail as well as trying to think of a way around my problem. I have seen commercial products that use MS SQL Server as a back-end that achieve an object-oriented database presentation so I was hoping someone could explain how it is done.

2) My "Notes" field / table unfortunately is for more than just products. The same database is also going to hold my PO records and my supplier information. I want to hold a notes field on each of these and it was this that was causing me concern (I know I could have a Notes table for Suppliers, 1 for PO's etc. but I wanted to try to get away with just the one!)

[wdmny]

Why don't you create a table that has columns without distinct names. For instance Val1, Val2, ..., Notes, etc...

Depending on the product, the value in the certain column will be different. Your code will determine what Val1 is based on the corresponding ID or whatever your relationship is to the master product table with their serial number, name, etc... For a monitor, Val1 may represent the monitor size but for a Printer it may represent the DPI.

If you have left over fields it wont matter since your code will know the product doesn't use those fields. I don't know if this approach would be recommended especially down the road, but for your purpose it should work.

[jofa]

This is how you can do it (see picture)
Every inventory item is of a certain inventory type and is located in some location
When you add a new item and select type, you (Access) look up what properties are related (tblInventoryTypeProperties) to this inventory type
Properties are of some property type and have a specific data type
In your inventory form you dynamically add new controls for the properties (different types of controls for different data types on properties)
The property _values_ are stored in tblInventoryProperty
The data type for this value field is plain text, with size that allows property data types yes/no, number, datetime, currency (not very efficient, but it works...)
Multiple notes for an item can be added in tblNotes
Events in the "life" of an item, such as "purchase", can be added to tblInventoryEvents
Any help?
JF

[jofa]

To make different types of Notes you must add a NoteTypeID field to tblNotes, and then only look for notes with relevant type ID _and_ the ID for the current row in Inventory, Supplier etc.

[pauls]

Thanks, Jofa!

I have to say I was about to start some serious coding to allow me to encode the details into a MEMO column, but that looked like hard work I was then tempted by Wes's suggestion but I was trying to decide how I would set it all up to cater for lookup fields etc etc. Now your solution looks like it may well be a flyer!

Damn I LOVE this site, I only posted my question a couple of days ago and already someone has handed me a solution on a plate!

Thank you everyone who responded.