SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Dec 2001
    Location
    Market Harborough, UK
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Any Access Guru's like to comment?

    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!
    Paul Simpson, BSc, MCNI, MCNE

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 .
    Nick Wilson [ - email - ]

  3. #3
    SitePoint Addict
    Join Date
    Dec 2001
    Location
    Market Harborough, UK
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hummmm

    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!)
    Paul Simpson, BSc, MCNI, MCNE

  4. #4
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Access, dynamic properties/fields

    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
    Attached Images Attached Images

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Correction...

    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.

  7. #7
    SitePoint Addict
    Join Date
    Dec 2001
    Location
    Market Harborough, UK
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks!

    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.
    Last edited by pauls; Mar 25, 2002 at 11:10.
    Paul Simpson, BSc, MCNI, MCNE


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •