Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Unanswered: n00b questions: database design help!

    Hi. I'm trying to create a database for a web application task for uni, but am having trouble getting my head around it. I can write sql and do the web page fine and dandy but designing the database is killing me.

    Backgroud; my group have decided on a "our city" community sort of thing but just for the a certian area. We are listing clubs, dinning and retail stores, their details and events. Events are like guest DJs for clubs, or half price lobster for dinning or specials like 50% of winter clothing for retail. There is also a comment section where visitors to the site can comment on the business and rate it.

    This is what I've come up with so far but it just looks wrong! Can someone who is knowlegable in this field perhaps tell me what I'm doing wrong and give me a few pointers.

    EDIT: image is attached to the post. I forgot I cannot like to that particular image store.

    Just a short description of the tables:

    business- has all the business info

    retail_details/dinning_details/club_details- the database has to be normalise to 3rd normal form so from the way I understand normalisation, it is nessecary to breat these catagories up.

    users- an added security feature talked about in the lecture. If the passwords were stored in business table the system could be compromised if some accidentally edited some sql to something like "select *; from businesses;..."

    comments- is there the user ratings and comments are going to be stored

    and events is where ALL the business events will be stored.

    ps- before you get your nickers in a nott. I'm not asking anyone to DO my work... I'm asking for advice and help and pointers.

    ta

    EDIT::: that {yes/no} was meant to be from reservation_required in dinning. woops... obviously my paint skillzors are lacking
    Attached Thumbnails Attached Thumbnails table.jpg  
    Last edited by jobe; 08-20-04 at 03:20.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    why the e_short_desc, e_imageurl, e_long_desc in events?
    there is an excuse for doing this, but don't present your schema before you work out what that excuse is.

    also, you have businessID as primary key in many tables - this makes no sense in almost all imaginable circumstances (only excuse i can think of would be if you ran over the 255-fields-per-table or some other db limit).
    ...and it also doesn't help you store your data: think about how you would add a second comment for a given businessID if comments has businessID as primary ????

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2004
    Posts
    7
    Quote Originally Posted by izyrider
    why the e_short_desc, e_imageurl, e_long_desc in events?
    there is an excuse for doing this, but don't present your schema before you work out what that excuse is.
    OK. firstly there is a long and short description of the event (the short is more of a caption) so that when multiple events are show on the web pages, there is only a short description to save space. These events link to a details page which shows the indepth description (ie. long description). It is a memo field.

    The e_imageurl is the url of an image that represents the event like a thumbnail photo of the DJ or whatever. This is an option field.

    Same thing is for the businesses (a short and long description and a picture representing the business).

    also, you have businessID as primary key in many tables - this makes no sense in almost all imaginable circumstances (only excuse i can think of would be if you ran over the 255-fields-per-table or some other db limit).
    ...and it also doesn't help you store your data: think about how you would add a second comment for a given businessID if comments has businessID as primary ????

    izy
    I originally had a comment_ID field (as an autonumber) as primary key for the comments table but like event_ID, it's only purpose was being unique and I thought in some strange way that it perhaps wasn't nessecary.

    As for the rest... well I really don't know. Can you please make some suggestions as to how I can fix it?

    Thanks alot

    EDIT: I am also considering scrapping the reservation required field in dinning and just having a simple if then else statment saying if field is null then reservation isn't required. or something to that effect
    Last edited by jobe; 08-20-04 at 13:20.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your long & short descriptions are fine once.
    i'm only asking you to make sure you have an explanation for why you copied (normally a db no-no) these fields into the events table when you could have looked them up in the main table.
    there is a reasonable explanation, by the way.

    the second observation about the multiplicity of tables with businessID primary keys is at the heart of relational db-think. if all the data in your other tables "belongs" to businessID then it should be in the main table. if it doesn't "belong" to businessID, it probably shouldnt have businessID as primary key ???? (maybe your "security" scam is OK, and you also must do this if you hit spec-limits of the db, but i can't think of another excuse!)

    ...and you still didn't explain how you are going to have more than one comment per businessID (...given that businessID is primary in your comments table). maybe that dumb autonumber that apparently only had the purpose of being unique was actually doing something useful????

    3rd normal is not about having many tables with the SAME primary key... in fact, it is almost exactly the opposite. each "species" has it's own table and it's own primary key... other tables refer to these keys as foreign keys.

    try some research into "foreign key" on the web, here, help, wherever.

    tv and red wine for me, internet research for you

    happy weekend.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2004
    Posts
    7
    Quote Originally Posted by izyrider
    your long & short descriptions are fine once.
    i'm only asking you to make sure you have an explanation for why you copied (normally a db no-no) these fields into the events table when you could have looked them up in the main table.
    there is a reasonable explanation, by the way.
    oh I see. I think there has been a miss-understanding. I have a short and long description for each event in the events table so that the short event can be used when listing mulitple events. Otherwise the screen would go on forever.

    ASLO- there is the same system for businesses. Ie- a short description used for when listing multiple businesses and a long description used for when viewing a single businesses.

    the second observation about the multiplicity of tables with businessID primary keys is at the heart of relational db-think. if all the data in your other tables "belongs" to businessID then it should be in the main table. if it doesn't "belong" to businessID, it probably shouldnt have businessID as primary key ???? (maybe your "security" scam is OK, and you also must do this if you hit spec-limits of the db, but i can't think of another excuse!)

    ...and you still didn't explain how you are going to have more than one comment per businessID (...given that businessID is primary in your comments table). maybe that dumb autonumber that apparently only had the purpose of being unique was actually doing something useful????

    3rd normal is not about having many tables with the SAME primary key... in fact, it is almost exactly the opposite. each "species" has it's own table and it's own primary key... other tables refer to these keys as foreign keys.

    try some research into "foreign key" on the web, here, help, wherever.

    tv and red wine for me, internet research for you

    happy weekend.

    izy
    ignoring the user table for a minute... I was considering putting the business, retail_details, dinning_details and clubs_details ALL into one table. But in that case, the last few fields which are specific to clubs/dinning or retail would have to be option fields, I could not enfore mandatory constrains like "all dinning should list their food type".

    Do you think they should all be one table?

    I obviously couldn't put the comments or events into the same table with the businesses because that would mean all the details for the business would be repeated on every line for every event or comment for that business.

    I will also put event_ID into the events table for a primary key. But what if I just don't have a primary key and just have business_ID as the foriegn key?

    Thanks alot for your help so far.

  6. #6
    Join Date
    Aug 2004
    Location
    NY
    Posts
    113
    Are you going to "Enforce Referential Integrity"? Are you going to input all the information in one form? If you do, will you not have to have data in all the tables, if you enforce ref integrity?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oooops, it's my limited reading skills getting in the way.

    events:
    i now understand that e_desc is nothing to do with b-desc: your events table looks ok. for a foolish moment i thought e-desc was a copy of b-desc but it clearly is not now that i look at your original post again.
    sorry that i misunderstood the gameplan.

    x_details:
    i now see what you are getting at here as well: not all businesses have entries in all x_details tables (maybe even that each business has one entry in only one of the x_detail tables as determined by type).
    if you are certain that there will be maximum one entry in each x_details table for each businessID, then using businessID as primary works fine.
    but a question about retails_details: you have a field brands (plural) suggesting more than one brand??? ...is this better handled with a record for each brand/business pair? ...if yes, you can't use businessID as primary.

    comments:
    how many comments/ratings do you allow per businessID?
    0 or 1 - you could use your original layout (same situation as x_details)
    1 - it belongs in the main table
    0, 1, or more - you need a different primary key

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Aug 2004
    Posts
    7
    ok... so can you please review the following and re-assure me that it is right. I still think it's strange having every ralationship centered around b_ID of the businesses table but I've sat here going over it again and again and thats the only way I can think of it.

    just to prevent any further confusion

    1- a business is either a club/retail or dinning (never both). Every business will have an entry in the business table and a SINGLE entry in ONLY ONE OF the x_details tables (ie... a club will have an entry in club_details etc). From what I understand... this is to comply with 1st normal form
    Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key)
    ie- grouping related data (business types)

    2- a business has many upcomming events

    3- comments are left by the businesses customers and relate to the business not events. A business will obviously have many customers

    4- brands in retails will just be a string of text (all brands listed in one field). If I later need to search for specific brands I can either just you wild cards or perhaps later split the table up.

    any field starting with b_XXX is b for business
    any field starting with c_XXX is c for comments
    any field starting with e_XXX is e for events

    e_caption and b_caption are the previous _short_description which is used for displaying multiple events/businesses on a page.

    e_desc and b_desc are the previous _long_descriptions which are used when viewing a single business/event on a page.

    pwd is password (I forgot that access allows keywords as fields but gheys up when you try to access them in queries without using [ and ].

    um... I hope that's right.

    ps- thanks for all your help. Would you believe that I'm doing this web apps unit at the same time as data moddeling. Would have been really useful to do data moddeling before this web apps unit but ahh well.

    ps- thanks again
    Attached Thumbnails Attached Thumbnails tables.JPG  
    Last edited by jobe; 08-24-04 at 02:44.

Posting Permissions

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