database performance

Anon

Hi all!

I have one big table that I am using for 2 kinds of data.

let say infodata and productdata

infodata needs the following fields

titre,description,picture

productdata needs

titre,description,picture, taxe,price1,price2

I suppose that is better to have 2 tables as below

table1

id,titre,description,picture,datatype

table2

id,taxe,price1,price2

but when the datas are called i need to show the products info (taxe,price1,price2) [if it is a product that is called]

And I am wondering what is faster to process
one big table or two tables ???

Notes:
the 2 kinds of datas are generally called separately, i.e. users are asking for a list of datas of one kind at once

Does someone can advise me?

smarlowe

two tables for sure.

Use an autoincrementing field in one as a key to the related entriesin the other table, then join them similar to this:

select * form table1 t1, table2 t2 where t1.id=t2.t1_id;

Make sure and index the fields used to join the two tables for better performance.

Anon

Thanks Scott,

What about 2 separated tables as below

table1

id,titre,description,picture

table2

id,titre,description,picture,taxe,price1,price2

Do you have an advice?

smarlowe

Close, but don't duplicate data in one table in another, so make the tables like this:

table1

id,titre,description,picture

table2

t1_id,taxe,price1,price2

then join them up like:

select * from table1 t1, table2 t2 where t1.id=t2.t1_id AND where t1.id=854;

This query would retrieve the rows in t1 and t2 that had the id 854 associated with them.

Anon

Not sure to understand cause naormally
there would not be duplicated data in one table in another, only duplicate fields but with diferent datas in it

table1

id,titre,description,picture

table2

id,titre,description,picture,taxe,price1,price2

table1 would hold the infodatas
table2 would hold the productdatas

another example to illustrate:

let's say that I have 2 kinds of people to care about:
suppliers and customers

I need the datas below for both

company,adress,town,state,tel,fax

but from the suppliers i also need to have

industry, year of establishment,comment

what is better ?

to have 2 tables as you propose to do

table1

id,titre,description,picture

table2

t1_id,taxe,price1,price2

or 2 separated tables

table1 (customer)

company,adress,town,state,tel,fax

table2 (supplier)

company,adress,town,state,tel,fax,industry, year of establishment,comment

Anon

What you need to do is let Scott know what type of data you are going to need.

This stuff here:

table1 (customer)

company,adress,town,state,tel,fax

table2 (supplier)

company,adress,town,state,tel,fax,industry, year of establishment,comment

is alot different then pictures, prices, and taxes.

Scott has given you tons of information to help you however, you did not give him all the information.

Some questions that you need to ask first is how important is your data?

Is it okay to lose data?

Is performance more important then reliability or integrety?

After you answer these questions then you can decide on the specific schema that you will need.

For one you need to look at address. Is this atomic or do you need to break that up into seperate parts.

Other questions are how many customers do you expect to have in your database and how many suppliers. This is important when you are searching, joining, inserting, deleting.

Anon

my apologize guys if I "hurt" you,
not in purpose !

I am novice with sql server and would like to model my database as well as possible.

What is most important in my case is performance.

about the 2 examples tha I gave to Scott,
both are inside my database and present the same characteristic so no matter which example we take into account.

so let say the we choose the supplier example

Is it okay to lose data?

What do you mean by losing data

how many customers do you expect to have in your database and how many suppliers.

I am expecting to have thousands of customers and a few suppliers (less than 200)

What I mainly want to know is how the database model is influencing on the performance?

what is better in terms of performance select in a big table or agregation of sevral tables?
...

Anon

Dude, I'm not upset... It's just hard to bridge the language gap some days. No need to worry.

Yeah, I misunderstood you. If you are gonna store a different dataset in those two different fields then you're fine.

So what you're saying is you have two kinds of product, some of which needs data in certain fields, some needs data in other fields, and you aren't sure if you should break them out into two tables or not because their formats are a little different.

So now you need to ask yourself if these two sets of products really belong together or not based on what you are gonna do with them.

Maybe they'll be used to fill in a page in alphabetical order, both types together a lot. If that's the case, leave them in one table and just leave the unused fields NULL and program your display code to know what to do with a NULL field, if anything even needs to be done.

If they will only rarely be merged into one data set, then you might want to keep them apart for simplicity's sake.

Either one would work here, but it depends on what you're gonna do with the data.

Anon

Many Thanks Scott,

one more question,
In his answer Ray wrote:

Is it okay to lose data?

What does that mean?

Is performance more important than reliability or integrety?

What should I take into account?