Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Best way to build a database?

    Hello,

    I am currently to better my database building skill and just have a question or two.

    The current database I'm trying to build is an auction database with information for guests, the auction items, financial information, etc.

    I haven't never fully 100% built my own database from scratch and have always had some of it built prior to me getting it.

    What I'm curious is how do most people begin building their databases? Do they just put all relative information into a table or two and then begin normalizing it by pulling out related columns and putting them into their own table?

    I'm just curious as to what the best way to finish this is. I'm a little bit into it but I'm still having trouble relating all the tables together. It's making my mind run a thousand miles a second it feels just trying to wrap my head around how this can work.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You definitely want to start with the data. The data will dictate the rest of the application. Based on your description you are going to probably want several tables:

    Guests/Bidders - The people bidding on the auctions
    Auctions - Item, start time, end time, type of auction
    Bids - Auction ID, Time of the bid, BidderID, etc.

    The above is considered a Many-To-Many releationship and is created using three tables. The way you can tell what type of relationship it is, is to ask yourself:

    1. Can a bidder bid on multiple auctions (Yes)
    2. Can an auction have more than one bidder (Yes)

    So you Bids table is what links the Bidders to the Auctions using the BidderID and the AuctionID (who is bidding and on what auction).

    You might also have an Items table where you store details about each item. The table would store the owner of the item, a description of the item, the opening bid amount, the reserve amount, etc.

    You might also create a Sellers table with the name, address, phone of the sellers of the auction items. In this case you would add the SellerID to the Items table. And in this case the Sellers and Items are related by a One-To-Many relationship.

    1. Can a seller sell more than one item? (yes)
    2. Can an item have more than one seller? (Probably not)

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is how I usually work:
    1. Define the business rules of the application.
    2. Define the "visible" data the application must handle.
    3. Define how they must logically be grouped (tables).
    4. Define the constraints to which they must obey (uniqueness, range of values, etc.)
    5. Define the relationships between each logical group of data (= each table): One-to-One, One-to-Many, Many-to-Many.
    6. Define the indexes and primary keys (natural or surrogates?).
    7. If necessary, define the auxilary tables: Lookup, Junction (for Many-to-Many relationships), Numbers (or "Tally") table(s)...
    8. Draw a schema of the database the way you just defined it.
    9. Check and, if necessary, modify the schema.
    10. When you're satisfied with the schema, review the business rules and the constraints and define how you will implement them: data definition or code. When possible, also choose data definition over code.
    11. Create the tables with their indexes and their constraints.
    12. Create the relationships.
    13. Check the validity of the data model by entering some data into the tables. If necessary, create some test functions or interface (form) to perform the tests.
    14. Begin developing the basic functions, then the interfaces (forms and reports).

    At every step of the process: take written notes and document your work.
    Have a nice day!

Posting Permissions

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