SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data Model > MYSQL Schema - Problem

    Hi Guys,

    I am going round in circles trying to sort this out.

    I wish to move my design to the next step. I am using MYSQL for my database.

    I am not sure if i am to include the fields that are duplicated in other tables (foreign keys) or do i add them at a later data?

    Can anyone help put me in the right direction so i can complete the schema including relationships, foreign keys etc.

    PLEASE SEE ATTATCHED MODEL





    Many Thanks

    Richard Howells
    my email

  2. #2
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't have to include the fields from other tables - the foreign keys provide the link to that, subsequent JOIN queries can fetch data from associated tables without the need for duplicated fields.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay,
    I think I know what you mean.

    So for my schema, should it just include the 'create table and create the fields'?

    How do i tell sql that there are foreign keys and relationships within the tables?

    Or am i missing the point?

    Rich

  4. #4
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't have to tell MySQL that, just make sure that you use the same datatypes for the keys across all tables (which isn't MySQL specific mind you). MySQL doesn't have a 'relationships' window like Access in which you define the stuff. Just design the tables and your scripts do the rest.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you must also be clear on the purpose of a model

    the model is intended to show the logical structure of the data

    in that regard, it has primary and foreign keys

    now, when you go to implement your model, you translate it into a physical design, which, if you are using mysql myisam tables, does not support foreign keys

    so, a recap -- the model includes foreign keys, even if mysql doesn't support them

    now, at the risk of confusing you further, you should nevertheless define the foreign keys in your CREATE TABLE statements anyway!

    even though myisam tables don't support them, the syntax is checked and therefore it acts, at the very least, as documentation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    p.s. and you still have an extra table between customers and products
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thanks,

    that is there because otherwise there would be a many to many relationships, because of this an extra entity is needed.

    Or am i wrong here ?

    richard

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PS this tables stores or should I say tracks the products that a customer views.

    richard

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    customer_product_inspection primary key is the composite of customer_id, product_id, and datetime_inspected (which is far better than separate date and time attributes)

    remove tables customer_interests and interests

    remove FK interests_id from products

    add new customer_interests table with customer_id FK, product_id FK, and primary key the composite of the two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ill edit the model asap and post it on here for you to have a look

    i appreciate your help

    cheers
    richard

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I decided to take out the customer_id and use the login_name as primary key for the customers because no to login in names can be the same

    also customers interests relates to the product types i.e memory, graphics cards, processors etc.

    hope this is okay for you
    thanks
    richard


  12. #12
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a thought !

    Isnt there now a many to many relationship between:

    customer_product_interest AND products tables ?


    Richard

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, the relationship from customer_product_interest to product is many-to-one

    by the way, drop column customer_product_interst, and make the PK the combination of login_name and product_id (not product_type)

    do not introduce surrogate keys into a logical model

    you may do so in the physical model, but only if you have a good reason, but for this design, you don't have one

    ditto customer_product_inspect, ditch that and make the PK the combination of the other three columns

    and remove interest_id from product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im not sure what you mean here:

    by the way, drop column customer_product_interst, and make the PK the combination of login_name and product_id (not product_type)
    Or here:

    ditto customer_product_inspect, ditch that and make the PK the combination of the other three columns

    and remove interest_id from product
    Ive attatched my model in word form


    Cheers
    Richard
    Attached Files Attached Files

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    word format is way worse than the images you posted earlier, so i didn't look at it

    you do not understand what i mean by "drop column customer_product_interst" ??? remove it from the model and diagram

    "make the PK the combination of login_name and product_id (not product_type)" assumes the relationship is from customer to product, not product type

    you didn't have a product type entity

    if you want a relationship between customer and product type, you have to have a product type entity

    i think you are getting frustrated by this process because you don't understand the basic method of modelling a relationship, and every suggestion you get from us is still confusing, and you are making revision after revision but you still don't understand what's going on

    just an observation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im sorry you are right It is confusing when im getting told to make changes etc to the model. But Im learning as Im going

    Ive added in the extra table called product_types because the customers interests are for product types e.g memory, graphics cards etc.

    These interests are entered when the user registers on the website. THen once they are logged in random products are displayed relating to their interests.

    Ive made the changes to the model please see earlier thread witrhin this discussion.

    I now am wondering if there is a problem between the relationships of the bottom three tables


    Cheers
    Richard

  17. #17
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, I already deleted one of the tables too many, and you put it back in!

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the latest model looks fine except for one thing -- you must still remove column customer_product_inspect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay ive up loaded the model again you can still view it in the disscusion.

    What do you reckon now to it ?

    Does it work for you?

    Hopefully its complete !!



    Cheers Richard

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i still see customer_product_inspect, and i've reloaded the diagram
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    Hey, I already deleted one of the tables too many, and you put it back in!

    yes appreciate your help asterix, but it seems the model has developed and changed slighlty now, what do you reckon to it?


    best regards
    richard

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    whoops, wait a sec, there we go, it's gone now

    however, the PK of customer_product_inspection should be all 3 columns, not just the first two, otherwise a customer will only be inspection-linked to a given product at most once (is that what you want?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There we go, done I hope.

    What do you think now.?


    however, the PK of customer_product_inspection should be all 3 columns, not just the first two, otherwise a customer will only be inspection-linked to a given product at most once (is that what you want?)

    It would make sense to have all three as PK.

    Many Thanks

    Richard

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, looks great, i hope we, er, i mean, i hope you get an A
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    many thanks for the help ill let you know how things go

    cheers again
    richard


Bookmarks

Posting Permissions

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