Results 1 to 10 of 10

Thread: Small MySQL DB

  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Small MySQL DB

    Im trying to get together a small mock database. I was wondering if anyone was really procient at MySQL that wouldnt mind writing up a small 7-10 table sports retail store kind of database?

    Like have tables like Customers, Employees, Sales, Products, Shoes, Shorts, Tshirts, Helmets. And just like the usual information for a person and just sizes, colors, gender, and brand kind of information for the products? I was trying to get an ER diagram I wrote to post but I feel like Im way off. I feel like this could take someone just a few minutes. Id be happy to help or contribute in any way but I just feel so lost on how to link the tables to eachother with the foreign keys and such.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sure
    my normal charge out rate is 50 per hour or part thereof.

    however if you make an effort at doing your coursework we may help you when you get stuck. but I doubt very much you will get any help if you don't make a reasonable fist of it first
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    I guess I'm caught haha but Theres an effort I have all those tables filled out like that but Idk how to write the sql statement to tie the relationship of shoes/shirts/shorts/helmets back to products and then products to sales then sales to customers.

    Im in my head that sounds like it would work but I may be way off.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried so far?
    what are you stuck on

    have you got your ERD yet?
    what differentiates Shoes, Shorts, Tshirts, Helmets from products

    what is the narrative you are developing to describe your design process (I don't want to know it but you need to develop one to explain why you took certain design decisions)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    Well all tables Shoes/shirts/shorts/helmets all will have the Product_ID (FK) which relates back to products table and the Product_ID(PK) From there it relates to customers, which also has Product_ID(FK) so in the end I should be able to add inventory to those 4 tables (Shoes/shirts/shorts/helmets ) and be able to look up customers by what theyve bought by the product_ID. Then just to have employees well for another table and for that to tie into customers also but more as of as Sold_to_customer_by column but itll be label Employee_ID.

    This is kinda what im trying to do.
    View image: DATA
    http://s23.postimage.org/bqk33it6j/DATA.png

  6. #6
    Join Date
    Mar 2013
    Posts
    6
    Idk if that last one went through. Im trying to build a database where I can add in data to these tables Shoes/shirts/shorts/helmets which all share the same FK Product_ID which then go back to the Products table which has Product_ID (PK) which is then share to Customers with Proudct_ID(FK) and then have an employees table which is shared to customers with Employee_ID kinda as like hey this guy sold this customer these products.

    Here is a diagram I through together.
    View image: DATA

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the problem you have is?
    where do you think the model is weak
    does it support what you think the business needs
    where is it exposed to problems
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2013
    Posts
    6
    I guess Im saying I cant figure out the code to link them? I can code to make the tables and define the primary and foreign keys, but is that it? I mean I feel like this is not enough done to get it to work. Or theoreticly by that diagram alone it should?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you believe your current design is normalised
    Tony Marston's Page on PHP/MySQL stuff
    Fundamentals of Relational Database Design -- r937.com
    do you believe you have developed and refined your ERD
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    Write down what you want to do:
    • You want to sell products which are categorized.
    • You have customers who buy the one or more product
    • You have employees who help one or more customer
    • and so on

    The you translate that into tables, to give you a headstart:

    table categories; cat_id, cat_name
    - 1, shirts
    - 2, shoes
    - 3, shorts
    - 4, helmets

    table products; product_id(PK), product_name, cat_id(FK categories.cat_id)

Tags for this Thread

Posting Permissions

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