Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Need some database advice please

Options
  • 21-03-2013 1:01am
    #1
    darragh o meara
    Registered Users Posts: 3,221 ✭✭✭


    As part of a system im working on I need to build a database, The basic jist of what I want to do is:

    1. Scan barcode/rfid tag (each item will have unique id number )
    2. System scans database and checks status i.e.. In stock, Out of stock
    3. If in stock, User is prompted for his user ID and item is logged to him
    4. If item is out of stock, then the user is prompted to put this item back into stock.

    Both checking in and out should store information like Id, Date/time, User id. in the database.

    Also in the database I want to be able to store information like :

    Parts:
    Supplier
    Unit cost
    Depreciation
    Condition
    Location
    User history
    etc

    User:
    Name
    Id number
    Phone number
    Email
    History
    etc

    There is other information that I will want to add into the database over time but this is what I can think of off hand.

    Now to my question, What would be my best and easiest course of action to build such a database. I have tried to link the tables in MS Access but I cant get it to do what I want. Ive heard that a language like SQL or Visual Basic may be a better option for adding the logic needed for this, Ideally Id like to make this a standalone operation.

    Im going to be using Labview to handle all the incoming data from the RFID reader and barcode reader so this information will be sent into the database as keystrokes so this side of things is not really important.

    Any help would be greatly appreciated.

    P.s; if a programming language looks like my choice can ye please advise a good compiler/ resources for getting a quick handle on the language as I ideally would like to get this sorted in the next 5 weeks.


Comments

  • Options
    #2
    Mort5000
    Registered Users Posts: 419 ✭✭Mort5000


    Is this an existing system that you're adding on to?

    As databases go, I wouldn't use Access unless you're certain the system is going to remain small for the foreseeable future.

    Database design sounds like it needs the most help.
    Can you have multiple PARTS for different suppliers? Multiple locations? Different depreciation values? Different conditions?
    User History probably belongs on a new table.
    Difficult to comment much more without knowing exactly what the business process is.

    A programming language would be essential for this processing. VB is fine, but it'll come down to which language you're most familiar with, combined with your budget.


  • Options
    #3
    azzeretti
    Registered Users Posts: 1,477 ✭✭✭azzeretti


    Is this for a library system?


  • Options
    #4
    darragh o meara
    Registered Users Posts: 3,221 ✭✭✭darragh o meara


    Mort5000 wrote: »
    Is this an existing system that you're adding on to?

    As databases go, I wouldn't use Access unless you're certain the system is going to remain small for the foreseeable future.

    Database design sounds like it needs the most help.
    Can you have multiple PARTS for different suppliers? Multiple locations? Different depreciation values? Different conditions?
    User History probably belongs on a new table.
    Difficult to comment much more without knowing exactly what the business process is.

    A programming language would be essential for this processing. VB is fine, but it'll come down to which language you're most familiar with, combined with your budget.

    In responce to your questions:

    1. No this is not an add on to anything already existing.
    2. Yes I will have multiple parts from multiple suppliers, along with multiple locations, Depreciation while not important will be a sliding scale ie 5% per year and conditions will vary between New,good,fair and bad condition.

    3. To give you an idea what I'm using it for; Im designing an automated storage retrieval system. The mechanics of this will be simply controlled by a user inputting a storage location ie: A1. In essence the machine will be dumb. To keep an eye on the tracking of the items I'm using an RFID reader to scan the items as they are being removed from the tray which I hope will output the ID number in the form of a keystroke style output, then onto a database to keep track of who and when takes the item.

    I dont have much programming experience away from C and even that is mostly handled by Labview. I need to learn a language in the coming weeks and get something running.
    azzeretti wrote: »
    Is this for a library system?

    No but a similar setup would suffice


  • Options
    #5
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    There are a number of fundamental rules you need to remember when designing databases.

    In layman's terms, isolate all data. Suppliers, items, depreciation rates, users, user history, etc. all should be in separate tables.

    Can an item have multiple suppliers? Can a supplier have multiple items (sounds logical)? Are there bands of depreciation rates (sounds like it)? If so, they can be applied to different items, therefore should be stored independently.

    Sounds like you need the following tables in your database:
    Suppliers
    Depreciation rates
    Users
    User history
    Conditions
    Items

    Each of them that have a connection (are "related" in DB terms) have a Primary Key/foreign key relationship. For example, can you have a User History entry, without a corresponding User entry? No, it doesn't make sense. So the History table would have a foreign key that is connected to the User table's primary key, corresponding to the user that did something to warrant an entry in the History table.


  • Options
    #6
    SalteeDog
    Registered Users Posts: 450 ✭✭SalteeDog


    Why do you need to learn a language? Are you sure that LabVIEW cannot run the application logic that you need.

    Or perhaps what you mean is you need to learn a Database technology?. Most databases use a flavour of SQL so learn the basics of that.

    Also - as previous poster has said - make sure you spend time in designing your database. Tables, fields, relationships and document the types of transactions that can happen and how they affect the data.

    If you already successfully managed a connection to MSAccess then it may suffice (if eg. you are developing a prototype or if scalability/distribution isn't a consideration). Alternatively look at SQLServer. There are other technologies that may suffice but it'll depend on whether labVIEW has libraries to support connections to them.


  • Advertisement
  • Options
    #7
    GreeBo
    Registered Users Posts: 27,073 ✭✭✭✭GreeBo


    OP it might be helpful to take/read some DB Normalization tutorials
    Basically you start with everything in one single table and then you normalize it by taking out duplication and replacing it with links (keys) into another table.
    You do this multiples times until your DB is fully normalized.

    Id almost forget about everything else until you have your DB schema finalized tbh.
    Also Id use something like mySql rather than Access...


Advertisement