about creating tables 1

[A1Pat]

Hi all,

I have a question about creating tables for my database.

Should I create 1 Product table for all products or,

Should I create many different tables for every different product?

Which way is more ideal to maintenance/search a database?

Thanks!!!

 

[amorous]

1 product table will suffice for all products...

something like this:

Table1 Name: PRODUCT

Fields:
ProductID----PrimaryKey
ProductName
Price
etc....

and You can have ProductID as a foreign key in other tables..


-VJ

 

[A1Pat]

Is it taking a longer time to search for a certain product in 1 general table than to search in a particular table of that similar product type?

 

[Krickles]

How many products? Unless you are dealing with many million, I would follow amorous' suggestion.

If you have a table per product type you must incorporate a product_type or product_category look up table when writing queries or hard code your queries. Depending on the degree of normalization, you still might need a look up table...but you can design in a more dynamic manner with one product table containing all products.

Additionally, if you have a table per product type the application will be very static. Each time the company introduces a new product type you will have to create a new table and update your queries.

Krickles | 1.6180

 

[A1Pat]

Very true... Thank you both Krickles and Amorous.

 

[Tarwn]

Why not post some examples of fields you need to stor, we may be able to help you compres the amount of data by suggesting a better layout for your tables. I actually wouldn't suggest only a single table for products (yet) simply because I don't know what kind of data your going to be storing.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[A1Pat]

Basically Tarwn, most of the products have many similar table's columns. It just that one product might has more fields than another, and so.

The reason I asked is because I concerned about whether or not it would be faster to work with the table or tables. But now I agreed with Krickles about dynamic database that I, in the future, don't need to go back save re-create a new product table when we have a new product.

 

[DreXor]

another benefit about designing from the beginning for dynamics, at a later point it would make it easier to add more dynamics for example product features which can be cross related to many products, including things like color.
most useful thing in learned in the past with large product bases is the use of catagories and sub catagories, and being very strict on the assignments of catagories. the use of just these two classifications properly can boost your performance and reduce data srver load and network data traffic tremendously

DreX
aKa - Robert

 

[A1Pat]

Good point. Thank Drex.