1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • MariaDB for warehouse (school exercise)

    From ^Bart@21:1/5 to All on Sat Jan 18 16:01:55 2020
    Hi Guys,

    I should help a person with something like a school exercise, we suppose
    to manage boxes for a warehouse:

    warehouse
    ------------------------------------
    warehouse_id name
    1 Dep01
    2 Dep02
    3 Dep03

    things
    -------------------------------------
    thing_id name available
    1 Printer cable Yes
    2 Tv Yes
    3 Pc Yes
    4 Smartphone Yes
    5 Empty No

    boxes
    --------------------------------------
    box_id name thing_id warehouse_id
    1 Box01 1 1
    2 Box02 2 1
    3 Box03 3 1
    4 Box04 5 1
    5 Box01 3 1

    Maybe I should use a table to store every boxes names and another one to
    store things linked to the same box name...

    ^Bart

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Kees Nuyt@21:1/5 to [email protected] on Sat Jan 18 17:29:18 2020
    On Sat, 18 Jan 2020 16:01:55 +0100, ^Bart <[email protected]> wrote:

    Hi Guys,

    I should help a person with something like a school exercise, we suppose
    to manage boxes for a warehouse:

    warehouse
    ------------------------------------
    warehouse_id name
    1 Dep01
    2 Dep02
    3 Dep03

    things
    -------------------------------------
    thing_id name available
    1 Printer cable Yes
    2 Tv Yes
    3 Pc Yes
    4 Smartphone Yes
    5 Empty No

    boxes
    --------------------------------------
    box_id name thing_id warehouse_id
    1 Box01 1 1
    2 Box02 2 1
    3 Box03 3 1
    4 Box04 5 1
    5 Box01 3 1

    Maybe I should use a table to store every boxes names and another one to >store things linked to the same box name...


    Assuming every Thing is in a box, and every Box
    is in a Warehouse, I would suggest:

    Warehouses(
    w_id integer prinary key not null
    , name varchar unique
    )

    Boxes(
    b_id integer prinary key not null
    , location integer references Warehouses(w_id)
    , name varchar unique
    )

    Things(
    t_id integer prinary key not null
    , box integer references Boxes(b_id)
    , name varchar
    , available enum(true,false)
    )

    If some things are not in a box, you would need:

    Warehouses as above
    Boxes as above
    Things(
    t_id integer prinary key not null
    , name varchar
    , available enum(true,false)
    )

    Thing_Box(
    thing integer references Things(t_id) not null
    , box integer references Boxes(b_id) not null
    , primary key (thing,box)
    )

    But it all depends on the requirements of your logistic operations.

    HTH
    --
    Regards,
    Kees Nuyt

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From ^Bart@21:1/5 to All on Tue Jan 21 20:38:36 2020
    If some things are not in a box, you would need:

    Warehouses as above
    Boxes as above
    Things(
    t_id integer prinary key not null
    , name varchar
    , available enum(true,false)
    )

    Thing_Box(
    thing integer references Things(t_id) not null
    , box integer references Boxes(b_id) not null
    , primary key (thing,box)
    )

    This is a good idea! :)

    But it all depends on the requirements of your logistic operations.

    Yes of course, now I should solve another "problem" about products which
    will be rent to others outside of the company, they will be stored in
    the things and thing_box but but after it when the product will go
    outside what happen?

    I thought to create a new record in warehouse table named "external"
    linked to a user's table, for external user I could add the product in
    things table because I don't know what happen outside.

    I'd like to try to use tables which already exist but maybe I should use
    a specific table for outside products...

    HTH

    ^Bart

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From The Natural Philosopher@21:1/5 to All on Wed Jan 22 10:33:38 2020
    On 21/01/2020 19:38, ^Bart wrote:
    Yes of course, now I should solve another "problem" about products which
    will be rent to others outside of the company, they will be stored in
    the things and thing_box but but after it when the product will go
    outside what happen?

    I thought to create a new record in warehouse table named "external"
    linked to a user's table, for external user I could add the product in
    things table because I don't know what happen outside.

    I'd like to try to use tables which already exist but maybe I should use
    a specific table for outside products...

    The normal way to flag products whichg are 'elsewhere' but still on a
    company's asset register is to create a different warehouse 'location'.

    'On hire' is just another 'location' with an address and contact details.

    So you need a table of 'warehouses' and then each part is tagged with a location field that points to the warehouse (or customer) location.

    Whether you add internal 'warehouses' to a 'customer' table or flag 'on
    hire' as a separate flag in the part table with a customer field, is up
    to the logistics of the business you are modelling.




    --
    "A point of view can be a dangerous luxury when substituted for insight
    and understanding".

    Marshall McLuhan

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)