Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43

    Irregular stock recording

    Ok, I'm trying really hard to build a database for a mobile phone company, and haveing massive difficulty on deciding on table designs for stock recording.

    Stock is not like normal shops, where you can just have

    stock(stock_code, make, model, price, supplier_id)

    With mobiles you need to track each induvidual unit, this is done with an IEMI number which is unique to every mobile.

    The problem I'm having is producing tables which dont contain massive amounts of redundent data or partially empty columns.

    at the moment i'm using the following design:

    stock(stock_code, make, model, price, supplier_id, type_code, qty) - normal stock
    stock_phone(IEMI, stock_code, delivery_id) - phone stock
    stock_SIM(stock_code, sim_num) - SIM stock
    stock_phone_SIM(IEMI, stock_code, sim_num) - phones with SIM
    stock_phone_SIM_NUM(IEMI, stock_code, sim_num, mob_num) - phones with SIM and number
    type(type_code)

    tariff(tariff_code, tariff_name, supplier_id, desc, value)

    delivery(delivery_id, supplier_id, stock_code, date)

    You see a mobile can come with or without a SIM, and that SIM can come with or without a mobile number. Thats why i've got seperate tables different types of phones. I'm also getting alot of repitition of stock_code. I'm not sure which way to tackle it, so any help would be appriciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    This is OK - a standard subtype design - as long as all the subtype tables (stock_phone, stock_sim, etc.) have a foreign key (stock_code) to the "supertype" table stock. Then all tables that deal with stock in general (like delivery) can reference the supertype, stock. Processes that need to look at the subtype details can then follow the link from stock to the relevant subtype table (based on the stock.type_code).

  3. #3
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Right, so it IS best to have a seperate table for a subtype of stock, allowing for any extra information required on that stock type? The problem I see is that removing or adding a stock_code would require updating the relation many times (ie in differnt tables).

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by jwab
    Right, so it IS best to have a seperate table for a subtype of stock, allowing for any extra information required on that stock type?
    Yes - or a single table with all the columns applicable to all the subtypes. See this thread for a discussion about the pros and cons of those 2 approaches. Start at around post #6.

    Quote Originally Posted by jwab
    The problem I see is that removing or adding a stock_code would require updating the relation many times (ie in differnt tables).
    Not sure what you mean by that: adding a stock item would require an INSERT into the stock table and an INSERT into the relevant subtype table, that is all.

Posting Permissions

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