Page 1 of 12 12311 ... LastLast
Results 1 to 15 of 180
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Design questions

    Hi, I am new to this board and this is my first post. I'm also new to database design. I have a couple of questions that have always nagged me and have gone unanswered I was hoping someone here could clarify for me.

    I have made a couple of small (4-5 table) databases and have them in at least BCNF. In adopting this method, while entering data, I am plagued with having to carry over to the next table, the primary key from the last table. It gets confusing at times because different tables have different primary key values. Is there a better way of designing this or going about this?

    Ex:

    personal_data_ID (PK)
    first_name
    etc

    address_ID (PK)
    personal_data_ID (FK)
    address

    city_ID (PK)
    address_ID (FK)

    personal data_ID, address_ID and city_ID PK's all are different and can become unwieldly to keep track of.



    Thanks, Frank
    Last edited by Frunkie; 03-10-07 at 21:59.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Well, you could use natural keys rather than surrogate keys. There is no law saying you have to use a surrogate key. But frankly if you find yourself having to "keep track" of a surrogate key then you don't fully understand what a surrogate key is.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by blindman
    Well, you could use natural keys rather than surrogate keys. There is no law saying you have to use a surrogate key. But frankly if you find yourself having to "keep track" of a surrogate key then you don't fully understand what a surrogate key is.
    Thanks for the reply. You are right, I didn't know what a surrogate key was until now. Well, at least I think I do. Please correct me if I am wrong but I now understand a surrogate key to be a "temporary" key. Or a key to use until a better key can be found. Yes?

    Actually, please allow me to give you one of my real world examples. I am attaching an image of a very simple database that I am beating my head against the wall over.

    My problem is twofold in that I don't completely understand surrogate keys and that I don't know if I am breaking the tables down too far. Everything looks great when its in one table.

    I really need to tie these tables together. I am just overwhelmed at all the FK's I will have to use to enter the data. I have concluded that I am doing something wrong here. Any insight would be greatly appriciated.
    Attached Thumbnails Attached Thumbnails export.jpg  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    everything looks great when it's in one table?

    how exactly do you see note, feedback, seller, buyer, and ad all in the same table?

    your diagram looks fine

    the seller-buyer-add structure looks at first like it might be a many-to-many relationship, and that the PK might be better as a composite key consisting of seller-id and buyer-id, but i don't think that would be right, i think the ad itself is a separate entity, which leads to the next question -- can only one buyer buy the item in the ad? what if no one has bought the item yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Smile

    Quote Originally Posted by r937
    everything looks great when it's in one table?

    how exactly do you see note, feedback, seller, buyer, and ad all in the same table?
    Well, I really can't see everything in the same table but from a noobie perspective, I would just LOVE to throw everything into one table and issue an auto-incrementing number and be done with it; but I know thats obviously not correct and afterall, I really am trying to learn and achive as close to perfection as I can with these tables.

    I really appriciate your help, I was at your site earlier and I just want to say that it feels comforting to know that someone who does this for a living is helping me out. Thanks.

    Let me explain the buyer and seller tables. What I have is an existing database that I want to tie these tables into. The already existing database has one table for the "users" that auto-increments. Now, I need to define the "user" from that table as either a buyer OR a seller. For example, today, user 30 may be a buyer and tomorrow may be a seller. For the purposes of the buyer and seller tables, I *think* it would be a "one to one" relationship.

    One buyer leaves feedback for one seller and only one seller. One seller receives feedback from one buyer and only one buyer. 1-1 no?

    The whole logic behind this addition is for a buyer to add feedback to a seller. I was going to use php to program a class to handle the insertion of the data.

    Here is roughly how I see the addition interfacing. The "user" or seller, logs in. The system already has that particular user's number from a dbsession. That user's number can occupy the user_Id PK.

    From that point, the buyer would enter the ad# into a textbox which would be placed into the ad_Id PK field. From there the buyer can enter his feedback into the additional textboxes for the feedback, item description and note.

    If you see a better way of doing what I am attempting, by all means.. I'm open.

    I'm sorry for the long post. I really do appriciate the direction here.

    Frank

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frank, don't worry about the "how" until you resolve the "what"

    don't think about the php that will maintain this data until you have the best data design

    here's an exercise -- create some rows of sample data

    try to make the sample values meaningful

    after you have created a few rows in each table, stop and look at the data, and see if you can spot anything weird

    does the data make sense in isolation? if not, why not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    frank, don't worry about the "how" until you resolve the "what"

    don't think about the php that will maintain this data until you have the best data design

    here's an exercise -- create some rows of sample data

    try to make the sample values meaningful

    after you have created a few rows in each table, stop and look at the data, and see if you can spot anything weird

    does the data make sense in isolation? if not, why not?
    Hi r937,

    Once again, thank you for the direction. That is also fantastic advice on forgetting about the "How" until I have completed the "What". Definately words to live by.

    Here is what I have done. In the excersise you had me do, I populated the seller, buyer and ad tables. The seller and buyer tables look great, just a bunch of unique numbers. Nothing jumps out at me as being wierd in these two tables.

    The ad table though, is a different story, I have a lot of numbers and a single "string" for a description. The FK's from the buyer and seller tables are a bit unwieldly but other than that, nothing jumps out at me. Am I missing something? (By the way, thanks for helping me to see issues and not just giving me the answers - That's how I seem to learn the best)

    I have completed the relationship between the "feedback" table and the "ad" table and have included another pic for you to look at.

    What I am having a hard time understanding is the fact the I *COULD* have tied the relationship of the feedback table to the "seller" table. To me, that seems more correct. My million dollar question has always been, when you have a table you are attempting to tie into another table via relationship and it makes sense both ways, where do you put it? At that point, I go back to my "business rules" and just hope that I have the relationships in their correct place. For example, with regard to the feeback table; my rule or "flow" chart says that a seller places an ad. The buyer buys the item in that ad, hence the two FK's in the ad table. The ad generates feedback and the note is included in that feedback.

    Now, here is where I get screwed up. If I rearrange those "rules" I can come up with serveral other possibilities for the relationships between the tables. I can find other ways that the relationships make sense.

    How do I know which is correct?

    Thanks again..

    Frank
    Attached Thumbnails Attached Thumbnails export.jpg  

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in my experience, a most valuable exercise is the exploration of one-to-many relationships

    for each line in your diagram, look at the two entities on either side and ask yourself does each X belong to only one Y, or to multiple Ys? does each Y belong to only one X, or to multiple Xs?

    then ask more general questions, like, can the same feedback be for more than one ad? is the feedback on the ad or the seller? what's the difference between a note and a feedback? can a feedback have more than one note? can you have feedback on the buyer? can an ad be for more than one buyer?

    messy, innit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    in my experience, a most valuable exercise is the exploration of one-to-many relationships

    for each line in your diagram, look at the two entities on either side and ask yourself does each X belong to only one Y, or to multiple Ys? does each Y belong to only one X, or to multiple Xs?

    then ask more general questions, like, can the same feedback be for more than one ad? is the feedback on the ad or the seller? what's the difference between a note and a feedback? can a feedback have more than one note? can you have feedback on the buyer? can an ad be for more than one buyer?

    messy, innit
    Messy? Absolutley! You know r937, it dawned on me that I have to be somewhat of a masochist because anything that would make me pull out the very last hair on my head then start plucking out the hair on my arms can't be a good thing, yet I can't look away.

    I am going to try your exercise and I will report back with what I find. Thank you!!

    Frank

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    for each line in your diagram, look at the two entities on either side and ask yourself does each X belong to only one Y, or to multiple Ys? does each Y belong to only one X, or to multiple Xs?
    r937, let me ask you a question. With these questions you want me to ask myself, are we talking for a single record insert or mutiple records?

    Does "does each X belong to only one Y, or to multiple Ys? "

    With regard to my diagram and the feedback/note tables: each note belongs to only one feedback and each feedback belongs to only note. But that answer would change if I said that feedback had many notes. See my issue? Feedback in the "Big scheme of things" has many notes attached to it but when looking at it from a single record insert, it only has one attached to it.

    The answer to the questions:

    a single feedback can only be for a single ad.
    the feedback is on the seller and not the ad.
    note is the term I am using because "Comment" is predefined in the db. (I had to find an alternative word to use) The note IS actually the sellers' feedback.
    a feedback can have only one note; the buyer. Ideally I would like to have the seller able to rebut the buyer but I don't want to tackle that right now.
    there is no feedback implemented against the buyer at this point
    an ad can have only one buyer.

    Thanks again for your help!

    Frank

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by fjm1967
    Please correct me if I am wrong but I now understand a surrogate key to be a "temporary" key. Or a key to use until a better key can be found.
    OK. You're wrong. That is not at all what a surrogate key is.
    A surrogate key is an artificial value for uniquely identifying a record. It is meant to be used solely by the database and should never be exposed to the user.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by blindman
    OK. You're wrong. That is not at all what a surrogate key is.
    A surrogate key is an artificial value for uniquely identifying a record. It is meant to be used solely by the database and should never be exposed to the user.
    Blindman, thanks for the clairification on that. So, a surrogate key for example would be a key, not really having any real meaning to the data other than being a unique value for a particular row. Correct?

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by fjm1967
    Blindman, thanks for the clairification on that. So, a surrogate key for example would be a key, not really having any real meaning to the data other than being a unique value for a particular row. Correct?
    Really "surrogate" is a pretty minor distinction. It's a key. You invented it instead of basing it on things you already knew.

    I liked r937's advice... here's something I try to do.

    Before I worry about all the technical stuff, I try to write in plain English what each part of the database means. I'm not talking about award winning prose, but rather, putting the logical meaning in my own words.

    For example, "In the table Personal_data the DB records the existence of persons. Each person is uniquely identified by an arbitrary (ID number), and has a (First Name) and a (Last Name) and is known to currently live at an (address)..."

    Then you write out a description of the actual data... "(Bob) (Smith) has ID number (45) and lives at (123 Main Street)..."

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by sco08y
    Really "surrogate" is a pretty minor distinction. It's a key. You invented it instead of basing it on things you already knew.

    I liked r937's advice... here's something I try to do.

    Before I worry about all the technical stuff, I try to write in plain English what each part of the database means. I'm not talking about award winning prose, but rather, putting the logical meaning in my own words.

    For example, "In the table Personal_data the DB records the existence of persons. Each person is uniquely identified by an arbitrary (ID number), and has a (First Name) and a (Last Name) and is known to currently live at an (address)..."

    Then you write out a description of the actual data... "(Bob) (Smith) has ID number (45) and lives at (123 Main Street)..."
    sco08y, thanks for the clairification. I was on the right track and think I had the right idea but I wasn't making much sense describing it.

    That is a great idea about writing what each database should do. I have done that on occassion with exception of "writing" it and just verbally "said" what it should do. Sometimes I get bogged down somehow when actually doing the physical model and seem to lose sight of what I just recited. I will try and write it down next time.

    Is it me, my inability to learn, or is this really "hairy" stuff?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    Is it me, my inability to learn, or is this really "hairy" stuff?
    well, this is not really hairy stuff, it's really simple and straightforward, so that narrows things down a bit, don't it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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