database help?

Question

i want some help regarding database design. for you expert people it is just for to answer. but it is new for me. i have made 5 tables in my database.
first one is customer which has
customerID
customername
address
second one is order.
OrderID
ordername
quantity
customername
third one is sales

transectionno
customername
ordername
quantity

i have question regarding relations. in second table i need customer name in order table and third table i need details from both first and second tables. so do i need to add their everything in each table or i just add customerID and ordername in respective tables. explain briefly please.

Answer 1

its looks like no one understanding your question.
your question is in second table and third table what fields you need to add to make a good relationship between all three.

i think you are not satisfied enough.

Answer 2

for the first table is called static table which mean that those table is just to provide the information for the other tables such as the second and third table

the second and third tables required primary key from the first table in order to create the relationship with the first table (customer table)

the third table also required the primary key from the second table
in relationship table to combine, so at the third table, the primary key are from the first and second table (combination from 2 primary key -- 2 fields)

in logic:
1st tbl_customer : customerID
2nd tbl_order : orderID
3rd tbl_sales : customerID, orderID

tbl_customer
| \
| \
| /|\
| tbl_order
| \|/
| /
| /
/|\ /|\
tbl_sales


the relationship:
tbl_customer --- 1 to many --- tbl_order
every (each) customer can have many order
tbl_order --- 1 to 1 --- tbl_sales
each order will (possibly) follow by 1 sales transaction
tbl_customer --- 1 to many --- tbl_sales
every (each) customer can involve in many (sales) transaction

to answer your question:
the other field at the third table will follow accordingly based on data in the first and the second table

Answer 3

you do not need to duplicate all the information in the tables to access them. when you have the tables "related" to each other by certain fields, you can use SQL to write a query to access the information in one table based on information from a different table.

for example, say you want to see the information in ithe customer table for the customer involved in transaction number 1. you would write an SQL query like this:

SELECT *
FROM customer
WHERE customername=(SELECT customername FROM sales WHERE transactionno=1)

what happens is you find the customer name for transaction number 1, and you find the customer information for the customer that matches that customer name.

what the above user said is also true, it's ALWAYS best to identify things meant to be separate with unique IDs. so in your sales table, it would be better to have fields for customerID and orderID. if you want to find the customer name or order name, use SQL to find the names in their respective tables using the IDs.

Answer 4

You should use primary keys that is customer id and order id in other table. because you had already stored the name of the customer in the table. now if you have two customers with same name then there may arise problem if you use customer name but two same customer name have different id. so you can use customer id. and with the help of customer id you can join these table like this
select * from order_table, customer_table where customer_table.customerid=order_table.customerid

if you like this solution mail me on [e-mail]

Answer 5

Your "order" and "sales" tables are known as Fact tables. Always use IDs (that is, integers, not strings) to refer to records from other tables (such as your "customer" table) in your Fact tables. You're on the right track!