SitePoint Sponsor

User Tag List

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

    Help with datamodel & relationships

    Hi Guys,

    Hope everyone is well.

    Im trying to create or should i say finish a data model. So i can go a head and create the sql for it. There are a couple of points im stuck with.

    The database im creating is for a 'ecommerce' solution as part of a university project. It is not a fully functioning solution, it dosent have shopping cart etc etc. It is more a marketing tool, utilising a product catalog and customer profiles.

    Every customer has a profile and within their profile they can enter 4 'interests'. When they log in sample products from the product table are shown which are related to their profile.

    This is where I have the problem, im unsure how or what to have in the 'interest' table. Also how to show the relationship between whats in the 'interests' table and the 'products' table.

    Ive attatched the model as it stands, any advice is greatley appreciated.

    Please note I am fairly new to all this
    Attached Files Attached Files

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here goes, I am typing as I see problems:

    1) Customer table. We don't store plaintext passwords, we store cryptographic hashes (MD5, SHA256 etc.) of the password. We probably also want to add a relationship to a "Country" entity.

    2) Cardinality of "customer_interest", it is most likely 0..4 (between zero and 4 entries)

    /deleted/

    4) There was no Primary Key on the "customer_product_inspection" entity. I chose one, but its not a natural key as far as I can tell.

    HTH
    Attached Files Attached Files

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

    re-vamped model

    Below shows my revamped model:

    What do you think?

    Cheers
    Richard


  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry mate, it's not improving:

    1) the Login Entity: requires a compound PK containing both attributes.

    2) Login again, either use * or n to represent any number in your model, but not both

    3) customer_interests, you introduced a new attribute for the PK. Not necessary, you can make a compound PK over both current FK attributes.

    4) Product_types.
    No need for this entity. Use the "posted attribute" method to post the PK named "interest_id" to the entity "products", so you have "products.interests_id".

    In Genereal: cardinality 1.1 can be shortened to 1.

    In specific (maybe too advanced) you don't show with your association lines if you are indicating identifying or non-identifying relationships. You know, the "crow's feet" things.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mmmmmmmmmmmm

    this is my first attempt at a model and a mysql database, it is for a uni project.

    1) the Login Entity: requires a compound PK containing both attributes.
    do you mean change both to PK

    3) customer_interests, you introduced a new attribute for the PK. Not necessary, you can make a compound PK over both current FK attributes.
    not sure what you mean

    4) Product_types.
    No need for this entity. Use the "posted attribute" method to post the PK named "interest_id" to the entity "products", so you have "products.interests_id".
    not to sure of your meaning


    please excuse my ignorance to this its a real headache for me this LOL

    ive attatched the model as it stands as a word doc please change it (if possible) so i can understand what you mean.

    regards
    richard
    Attached Files Attached Files

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here you go.
    Attached Files Attached Files

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wicked I see what you mean now,

    cheers.

    Do you see what Im trying to acheive with 'interests' this is part of the 'customers' profile. an interest is a 'product type' i.e memory, graphics card, processor etc etc.

    when the user logs in products are displayed on screen in a designated area to the site, that realtes to the profile.

    Cardinality of "customer_interest", it is most likely 0..4 (between zero and 4 entries)
    Does this mean a customer will have four interests? If so would this then be a constraint?

    Richard

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, its a relational constraint. Anywhere between 0 and 4 related records.

    Don't confuse relational constraints - like this one - with attribute constraints.

    Relational constraints define what the cardinality of two related sets is (0..4).
    Attribute constrainsts say what data is valid for an individual field (like saying data in the name field may only be letters, no numbers).

    BTW I only chose 0..4 because that's what you wrote in your original post: "can choose 4 interests."

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No that is fine i like your way of thinking,

    Do you feel i can now progress to the next level and actual create the schema for the database?

    If so I know how to create the database, tables, and fields, BUT when it comes to giving them the corect attributes, foreign keys within the tables and constraints im a little lost,,,,,,,,,,,,,,,well A LOT LOST LOL

    If i create the basic schema without mention to foreign keys, constraints etc will you please help and advise on how to add them, in order to get a great working/ fully functional database?

    If so I will be over the moon and it saves me a lot of time, especialy as this is a uni project

    best regards

    richard

  10. #10
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't you have a database to play around with?

    In MS Access or MSDE you can create this schema in about 10 minutes

  11. #11
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, you said MySQL

    Not too sure, last time I looked you couldn't create relational constraints in MySQL. Maybe they've improved it since then though.

  12. #12
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, you said MySQL

    Not too sure, last time I looked you couldn't create relational constraints in MySQL. Maybe they've improved it since then though

    Found this quote in one of my books:~

    I have used other RDBMS like Sybase and SQL Server 7. They have
    table constraints and checks. Does MySQL have these as well?
    A: The quick answer is no. However, MySQL can parse this syntax, so you can
    easily import existing schemas into MySQL without a lot of hassle.
    another:

    The next biggest detriment is the hassle and frustration that constraints can cause. Working around
    constraints, especially when deleting records, is a headache. This headache is multiplied tenfold when
    the database is poorly designed..
    another:
    MySQL does not support constraints. Constraints only add to the overhead and slow things down, which
    goes against the primary reason for using MySQL. The power is in the hands of the developer and the
    DBA. It is their responsibility to ensure data integrity and that table relationships are enforced. If you
    write good, database-aware applications and the schema is clear and easy to understand (good
    design), the need for constraints, much like triggers or any other extra feature, is minimal.
    What do you suggest now?

    Now I really am confused?

    For the purpose of this project I must use PHP & MYSQL

    Ahhhhhhhhhhhhhhhhh


    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)
    Quote Originally Posted by weewizard
    The power is in the hands of the developer and the
    DBA. It is their responsibility to ensure data integrity and that table relationships are enforced.
    i would be interested to know the name of the author who wrote that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol

    If you write good, database-aware applications and the schema is clear and easy to understand (good design), the need for constraints, much like triggers or any other extra feature, is minimal.
    Not read so much *bs* in a long time
    So much for set theory and predicate logic.

  15. #15
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey asterix

    just wondered why you took my product_types table out of the model?

    I mean that table stored the:
    product_type_id PK
    product_type_description (for example memory, graphics cards, processors)
    product_type_image


    now i only have a table that stores individual products:

    product_id PK
    product_type_id FK
    interest_id FK
    product_name (DDR 400 128 Mb Ram)
    product_description (This is the best Ram money can buy at presant bla bla)
    product_price
    product_image

    Any ideas

    Cheers, Richard

  16. #16
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by weewizard
    just wondered why you took my product_types table out of the model?

    I mean that table stored the:
    product_type_id PK
    product_type_description (for example memory, graphics cards, processors)
    product_type_image
    You can put the description and image attributes in the "Interests" table.
    Then change the name of the table to "Product_Type" LOL. Do you really want to store images in the database? It makes a lot of sense, but only if you know how to code it in PHP. Many people tend to store the path to an image stored on the file system instead of storing binary data in the database.

    No seriously, you need to consider what the "interests" entity represents. Most likely it is actually "Product_Types" that a customer is interested in? Also solves the problem of not yet having a text description for each "interest".


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
  •