Web programming TipsBy Sergey Skudaev
Let us continue learning how to create Microsoft Access database and tables.To plan what kind database tables we need to create in Microsoft Access database we have to consider the following questions.
What kind data we want to trace?
1. Information about credit card. Credit card name (like Bank One or CHASE ), credit card type (MasterCard, Visa etc) number (the last 4 digit), expiration date, phone, address, city, state, zip. notes
2. Information about transactions: balance, minimum payment, due date, paid date, paid amount, check number maybe something else. Be creative. Since, one credit card has many transactions we have to store transactions in the separate table. If we stored them in the same table then the credit card info would be repeated as many time as many transactions it has. We do not want to waste computer resources. Primary key is a field in a table that holds a unique value for an entity. For credit card unique value is credit card number, but also we can use auto incrementing number that is generated by system automatically. One credit card has many transactions. One transaction can be related only to one credit card. Relations between credit card table and transactions table is one to many.It means that we have to place credit card id (primary key in credit card table) in transactions table where it plays a role of the foreign key. You will see and understand later.
Hover your mouse over an image to enlarge it.
Open MS Access.
Select the Blank Access database radio button and click OK.
Enter file name credit.mdb and click the Create button.
Database will be created. Select Tables from Objects list and double click Create table in design view.
Table displays in design view.
Enter the first field: cardid and select Autonumber from Data type
Select No duplicates at the bottom. Enter the rest fields selecting appropriate data types. Save table and name it credit_cards. It is good habit to use one word to name tables.
You will be prompt to create primary key:
Answer Yes. Close the credit_cards table.
To create transaction table we will use the data definition language:
create table transactions(
transid int not null,
cardid int,balance currency,
min_payment currency,
due_date date,
paid_date date,
paid_amount currency,
check_number varchar(20)
);
,# int means integer. Not NULL means NULL is not allowed
# varchar(20) -means text 20 characters long. 255 allowed
Select Query in the objects panel and double click the Create query in Design view.
Show table dialog displays:
Click close button. Select View, SQL view in main menu:
Select Query window displays. Copy the script and paste in the window. Click Run ! button
Save query: Enter query name and click OK
Close the SQL view window. Select Tables from object panel. Select transactions table and open in design view.
Change transid data type to Autonumber.Close and Save the table. Enter dummy data in the tables: at least two credit cards and four transactions.
Credit cards.
Transactions
Select tools, relationships from main menu:
Show table window displays. Select tables (one at a time) and click the Add button. Click Close button. Select cardid from credit_cards table and drag it to cardid in transactions table. The Edit Relationships window displays.
Click the Create button.
A link appears between tables. Click X in the right upper corner of the relationships window. Answer Yes to save changes. You can create relationships in create table script. If you what to try, select transaction table and delete it. Answer yes to all prompts. Select Queries, Create query in Design View. Open View SQL and paste the following script.
create table transactions(
transid int not null PRIMARY KEY,
cardid int,
balance currency,
min_payment currency,
due_date date,
paid_date date,
paid_amount currency,
check_number varchar(20),
CONSTRAINT FK_CardID
FOREIGN KEY (cardid)
REFERENCES credit_cards(cardid)
);
Execute query by pressing "!" icon and go to Relationship. Add tables and see that they are linked to each other.
