Solved

Which Is Correct

Posted on 2011-09-21
8
326 Views
Last Modified: 2012-05-12
Hi, i am creating a small site which sells different types of products (i know there are several free off the shelf sites that are already built, so please do not let this influence your help)

I am thinking of setting up an ORDERS table which will holds various details about the order and then another table called PRODUCTS which will hold the various specific details about the product itself. Then i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order.

This is an example of both tables:

ORDERS
id
date
receiver
product-id

PRODUCTS
id
product-name
price

I have two questions which i hope you can help me with:

1 - as i mentioned above i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order... how should i name the fields in both tables considering there is an "id" field in both tables and a "product-id" field in the actual ORDER table...  i know you can work with 2 tables that have the same field name BUT is it best practice to avoid this... what would you suggest naming all of the above fields...

2 - i will be selling various different types of products (min of 5, max of 10), and all of these products have very different attributes that needs to be stored... so should i just make one big huge PRODUCTS table that will have a field that caters for all of the products and there attributes OR should i create a different table for each product type... for example some products will need a fields called "height", "width", "water-depth" etc... whereas other products do not have any values for these fields and need other set fields...

Thanks in advance for your help... looking forward to your feedback, thanks...
0
Comment
Question by:oo7ml
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Accepted Solution

by:
speak2ab earned 250 total points
ID: 36572670
For 1: I would recommend
ORDERS TABLE
order-ID
Product-ID

PRODUCTS TABLE
Product-ID

For 2: This depends on your specific scenario. From your statements, the design recommendation i will give you is that: You don't need to create different tables for each product type otherwise you will be creating redundant fields and you will end up requiring several JOIN statements when you need to link these tables, thereby losing performance and efficiency. Again this is a design consideration that depends on what you envisage. However creating a Product table with product types as an attribute should be the way to go.
0
 

Author Comment

by:oo7ml
ID: 36572731
Hi, thanks for your reply...

I have been reading on google that

1: you should not use - (minus) and should use _ (underscore) instead...
2: id should only be id and not order-id and product-id... you should only use that when linking a foreign key

What do you mean "creating a Product table with product types as an attribute should be the way to go."
0
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 250 total points
ID: 36573006
I would design the database differently

Clients
ID(PK)
UserName
Password
FirstName
...

Orders
ID (PK)
DateStamp
ClientID (FK)

Products
ID (PK)
ProductName
Price

ProductAttributes
ID(PK)
Name

OrderAttributes
OrderID (FK)
ProductID (FK)
AttributeID (FK)
AttributeValue

this structure will not restrict you in the future and will give you flexibility to enhance the site more

you can also make a table linking product and attributes and possible values


0
 
LVL 15

Expert Comment

by:Eyal
ID: 36573019
sorry missed important table

OrdersProducts
ID (PK)
OrderID (FK)
ProductID (FK)

and change the following:
OrderAttributes
OrdersProductsID (FK)
AttributeID (FK)
AttributeValue
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 5

Expert Comment

by:speak2ab
ID: 36573073
1: you should not use - (minus) and should use _ (underscore) instead...
I assumed you know that, I just followed your format to explain to you. Btw, you don't necessarily need to have even an underscore. You can have it as ProductID. There is no defined rules for the naming convention you use but it is important to be consistent. I particularly use prefixes for all items in my table.

2: id should only be id and not order-id and product-id... you should only use that when linking a foreign key
Again there is no hard rule to this, it really depends on your situation. This is mostly a practice for the study phase of databases. Imagine a situation where you need to find all the places where your field "ID" is used in some large script or program code. Searching for "ID" is meaningless (1000s of them!),

So my suggestions to you:
-it is recommended that every column name should be self explainatory or meaningful.
- common names and system identifiers like ID, NAME etc can be used  however such column names leads to confusion across data model. (The more you begin to code the more you will appreciate this)
-It is importand to keep consistency of column names across table wherever possible for better understaning of design, readability and to avoid mistakes while writing queries and to increase the efficiency.

Read More here:
http://www.smart-it-consulting.com/database/progress-database-design-guide/

Some best practices:
http://codebalance.blogspot.com/2011/07/20-database-design-best-practices.html

What do you mean "creating a Product table with product types as an attribute should be the way to go."

Read more about Normalization if you can. It is an important building block for DB design
http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

These should keep you going.
0
 

Author Comment

by:oo7ml
ID: 36573209
ok thanks, so would you suggest actually calling fields:

ORDERS
order_id_pk
product_id_fk

PRODUCTS
product_id_pk



0
 
LVL 5

Expert Comment

by:speak2ab
ID: 36573604
No drop the pk and fk.

Firstly, I will suggest OrderID and ProductID. Notice the use of CamelCase in my example. Notice that the word ID is also in block letters. This helps the readability and future maintainance.

Secondly The foreign key and the primary key essentially should be thesame. Except you have special reasons not to.

Thirdly: The underscore is cumbersome to type, I would recomend dropping it except if you have special preference for them.
0
 

Author Comment

by:oo7ml
ID: 36573625
Ok thanks... my only worry using those is that they are case sensitive... i will re-visit my design and see how it fits, thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Your suggestions for a Wordpress Theme 2 21
SQL BACKUP - 2008 R2 8 20
two tables one button 11 20
Insert data into database 2 15
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now