Results 1 to 13 of 13

Thread: Inventory DB ??

  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Post Unanswered: Inventory DB ??

    Hi Guys

    I thought asking this question here and maybe one of the MOD's will forward it to the right place. Sorry for any inconvenience caused!

    Ok here we go ...

    My friend recently opened a Tyre & Garage , where he will be selling & mounting Summer tires + winter tires as well Summer&Winter tires (2 in 1)

    He got no cashier and currently using double sided paper for customers.
    Never the less I know a bit of Database and thought if I can create an Inventory Database that keeps track of Stock as well then automatically deducts amount - for every sale it has been generated.

    I think it might need 2 or possibly 3 tables (correct me if I am wrong)
    Stock table + Sales table + not_sure_what_other_table

    Anyway ... this is what it should work / do:

    - input & record all tires (winter + summer and sum&win tires)
    - produce a receipt for every sale and also record all sales (date,amount etc)

    In a sense, I kind of have a picture of what to do but NOT CERTAIN.
    Would be great if you can guide me and give me some ideas or hints to this


    Much appreciated



    ==========================

    Please delete this post located at: http://www.dbforums.com/new-members-...entory-db.html

  2. #2
    Join Date
    Apr 2013
    Posts
    72
    hmmmmm

    I am confused!!
    If I create two tables ... this creates a table
    where I will have following data:

    TireType: WinterTire | Profile: 225/50/16 | Price: 29.99 | Qty: 50
    TireType: WinterTire | Profile: 225/55/16 | Price: 24.99 | Qty: 40

    as far as I know, ideally, there should not be a column with similar title?
    in this case "WinterTire" appears twice ... tho, the database will record over 5000 tires

    Should I create 3 tables: Tires + Stock + Sales

    TIRES

    Type* PK
    Profile
    Name
    Price


    STOCK

    AutoNumber
    Type* FK
    Quantity


    SALES

    Date
    SalesID * PK
    Type * FK
    Profile
    Name
    Price
    VAT
    QTY
    SUM



    Plss advice

  3. #3
    Join Date
    Apr 2013
    Posts
    72
    Question is:

    How will the database deduct the quantity amount
    from the stock table, once a sale has been made (via sales form)


    ps: been ages since I touched Access!!

  4. #4
    Join Date
    Apr 2013
    Posts
    72
    nobody ??????

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno where you get 2 possibly 3 tables from. I gave up counting after 11
    this model doesn't include all the columns you'd need
    it also doesn't include how you'd handle discounts or promotions probably another 2 or 3 tables

    like all such models it reflects somebodies experience, knowledge, and prejudices

    as to naming Ive used names which describe the story rather than names I'd actually want to use (eg Id probably use xxxxNo instead of xxxxID)

    dashed lines are non identifying relationships
    solid lines identifying realtionships

    I'd probably want to expand in the product area as tyres can come in different sizes, so Id probably want a ProductSizes table which classified different sizes of the same tyre

    If I was being a smarty pants then I might want to expand the model to include recommended tyres for a specific make and model of car (although thats perfectly easily handled outside the system using the standard auto industry references)
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2013
    Posts
    72
    HealDem

    Gosh, 11 tables?? really?? why???
    I mean, all the database should do is

    - be able to store lots of tires, that will include

    TireSize: 16" or 17" or 18" or 20" etc)
    TireType: Winter or Summer or Both
    TireProfile: 225/50 or 225/55 or 225/60 etc
    TireBrand: Not that relevant
    TirePrice:
    Quantity:

    Now, lets assume there are 100 tire types.
    I would need a SALES FORM , where I choose the TireSize + TireType + Profile
    and it should show the price + the current stock

    and then I can have a drop down box : 1 / 2 / 3 / 4
    meaning how many tires I am selling

    Now when I hit OK it should generate a receipt and then
    deduct the "amount" from the current stock.

    I do not need Customer details or suppliers

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You state you need to record sales. I dont know where you go to get your tyres sorted but the place i go to keeps records of who they sold to and when. Sonetimes thats used to give discounts on re purchases, it also includes notes so they have a history of who said what and when. Eg why a discount was offered last time.
    The number of tables required by a design directly reflects the aims and objectives of the application. The 3 tyres shops I know buy from a distributor not direct from a manufacturer. At least one buys from more than one distributor. A manufacturer makes many types of tyre and many sizes of makes if tyre. Granted there may be some make of tyre only iffered in one size but the principle holds.
    Any business needs to know what its stock is. Theres various types, free stock / stock on hand and fiorecast. Distributors may complete an order in one delivery or not. Strictly speaking one delivery may comprise bits from more than one order. The business needs to know what it has out on order but not yet fulfilled.
    If you are attemoting to design a normalised db then you would never store a stock level. You derive the stock in hand by using a query which subtracts sales from deliveries by product (make size and profile).

    Of course if you just want to knock up something quick and dirty then do what you feel is right. But get your design right before attempting to design the form. Its tempting to jump into form design way too quickly in Access, and bad choices made to early can cause significant problems at a later date.

    In all 3 tyre shops the make and brand is important, so they offer the customer a choice on price and brand. A customer may not care what makw and just want the cheapest, another may specify they want, say, Michelin because of preference or advertising. Perhaps you should go back to your mate and make certain what you are propsing works for him. He should have a clear idea of wgat his business actually needs.....

    ....unless this is coursework, in which case you can do whatever the heck you like. As the purpose of coursework is to demonstrate you have picked up the principles of what you have been tought
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    Heal

    I know where ur coming from but in this case
    my friend only needs something temporarily ... he uses a wholesaler website
    to sell new tires but he got 5000 tires that he already imported into UK.

    He does not need to keep track of customers.
    All it needs is to input the amount of tires with its data
    and then when someone buys 2 tires (example) then I should be able
    to generate a report/receipt for the customer and then it should deduct the amount
    from the stock ...

    not sure if I am being clear enough??


    cheers

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when a sale completes you don't "deduct from stock", when you need to calculate stock do it through a query subtracting sales from deliveries
    Im guessing your friend will also have to account for VAT (Quarterly), and either monthly or annual accounts.

    using his distributors website is all well and good for checking what tyre fits what vehicle. what he needs is a process that allows him to identify what tyres he has in stock to fit that vehicle. I guess it depends on the sales process, do peopel turn up unannounced requesting tyres for their vehicle (if so you can take the tyre size from the ones in front)

    therre are at least 4 search parameters, or at least Tyres Online | Cheap Tyres | Winter Tyres & Car Servicing UK suggests so, although in practice Im not too sure whether tyre customers care overmuch over the speed rating or if there is much choice
    add the manufacturer, tyre type and so on.

    in my books you need to do a more detailed analysis of the processes in your mates garage
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2013
    Posts
    72
    Heal

    Appreciate ur time and effort

    In this case then, do I need 11 tables ???

    Regards the customers: they turn up whenever they need to
    and the tire size/profile can be checked on the actual tire ...

    Example: he gets a car that needs front tires replaced: 178/55/R16
    which means, its 16" with 178 width and 55 height (something like that)

    Anyway, the databe could have 2 tables
    the stock (delivery) table and sales table - right???

    stock table has all tires available with price and quantity
    And the sales tables basically lets the user select:

    - which tire size : 16" or 17" or 18" etc
    - which tire type: winter or summer or both
    - which tire profile: 179/55 or 225/55 or whatever is in stock

    then it should automatically collect the price + quantity or that particular tire
    and then you say how many tires are being sold


    now question is : where is all this data stored??
    into the sales table, right???


    I hate it, I thought I knew ACCESS but seems like I lost it all

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need as many tables as needed to support the business requirement
    you are developing an application, you are supporting the application
    how you design the app is up to you. for me I see at least 11 tables (well actually 12 when you factor in the the tyre size.

    if you think you can do this using two tables, do so. but there is no way of linking sales with deliveries, you need a product table, well I suppose you could use the tyre specification as your product code and do away with a product table altogether and just wing it that there are no data errors, but you'd be violating normalisation. so your PK could be the tyre spec eg 178/55/R16, but straight away you have a problem. is the tyre spec 178/55/R16 or 178/55R16?
    how do you propose to identify what tyre is a winter, normal or winter + nbormal on your two table model, without violating normalisation.

    As I see it there are several processes or in UML speak use case diagrams
    customer arrives at shop asks for tyres to be replaced
    customer phones ahead to get prices, is quote a price, and that quote needs to be picked up when customer arrives, quote has a validity date.time range
    owner needs to know stock.
    owner needs to work out what to order (presumably in part based on what he has sold)
    owner needs to prepare periodic sales and purchases reports

    bone up on normalisation
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2013
    Posts
    72
    How do we know which tire is which??
    that is what I keep asking myself

    Because there are three types of tires - winter + summer + both
    so the PK could be a "compound key" of "TireSize and TireProfile"

    Right???

  13. #13
    Join Date
    Apr 2013
    Posts
    72
    To give a better overview, this is what I can think of
    what is required:

    Stock ID (autonumber)
    Tire Size
    Tire Type
    Tire Profile
    Tire Brand (not relevant really but anyway)
    Tire Price
    Quantity (quantity for each type of tire)

    Sales ID (autonumber)
    Sales Date
    Sales Staff (just an idea but not required)
    * Tire Size
    * Tire Type
    * Tire Profile
    * Tire Price (should show automatically including VAT)
    Quantity (the quantity that is being sold)

    -------------------------------

    Now, we can split all this into the following tables:

    STOCK TABLE

    StockID (PK)
    TireSize (FK)
    Quantity

    SIZE TABLE

    TireSize (PK)
    TireType (FK)
    TireProfile

    TYPE TABLE

    TireType (PK)
    TireBrand

    PRICE TABLE

    TireSize (FK)
    TireType (FK)
    TirePrice


    ===============

    Does this make sense???

Posting Permissions

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