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.
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)
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.