Database design question?

[perspolis]

hi
I have two table named Purchase and Sale..all of fields of
both tables are the same...I make them design in one table
with an additional boolean field to determine which is Sale and Purchase...
it is good to design them in seperate tables without additional field??
or design both in one table...

[Laurent Liegeois]

Re: Database design question?

Hi,
Personally, I prefer to have all data in the same table for avoiding
the redundancy but it can be (little) more difficult to make a good
sql if you need to compare Sale and Purchase ….ex(select * from
(select… from Sales=1) as Sale, (select …. from Purchase where
Purchase = 1) as Purchase from Purchase.id =Sale.id)
And with good index it will be very performant.

Otherwise, if you use sql server 2000, you have the index view it can
be very useful in your case.

Bye



On Tue, 21 Dec 2004 06:46:39 +0330, "perspolis" <rezarms@hotmai l.com>
wrote:
[color=blue]
>hi
>I have two table named Purchase and Sale..all of fields of
>both tables are the same...I make them design in one table
> with an additional boolean field to determine which is ...
>it is good to design them in seperate tables without additional field??
>or design both in one table...
>
>[/color]

[Pete Kane]

Re: Database design question?

Hello there, personally I would split them into two tables and link them
using their primary key - this allows you the flexibility to modify the
structure of the tables independantly of each other, a purchase or sale
could have an attribute which does not apply to both entities

regards

Pete Kane


"perspolis" <rezarms@hotmai l.com> wrote in message
news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=blue]
> hi
> I have two table named Purchase and Sale..all of fields of
> both tables are the same...I make them design in one table
> with an additional boolean field to determine which is Sale and
> Purchase...
> it is good to design them in seperate tables without additional field??
> or design both in one table...
>
>
>[/color]

[Ignacio Machin \( .NET/ C# MVP \)]

Re: Database design question?

Hi,

IT depends of several things among others:
1- How big the table can get
2- Concurrency issues
3- PK generation, maybe you want a different sequence for sales & purchases


If you decide to have both together you can create two views one for sales
and another for purchases and make all the operations agains them. this will
abstract the real table composition.

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



"perspolis" <rezarms@hotmai l.com> wrote in message
news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=blue]
> hi
> I have two table named Purchase and Sale..all of fields of
> both tables are the same...I make them design in one table
> with an additional boolean field to determine which is Sale and
> Purchase...
> it is good to design them in seperate tables without additional field??
> or design both in one table...
>
>
>[/color]

[perspolis]

Re: Database design question?

thx to all who reply me..
if tables for Sale and Purchase are big, I think it's better to have them
seperatedly...i sn't it??

"perspolis" <rezarms@hotmai l.com> wrote in message
news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=blue]
> hi
> I have two table named Purchase and Sale..all of fields of
> both tables are the same...I make them design in one table
> with an additional boolean field to determine which is Sale and[/color]
Purchase...[color=blue]
> it is good to design them in seperate tables without additional field??
> or design both in one table...
>
>
>[/color]

[Pete Kane]

Re: Database design question?

I would always go the two table route, the difference in storage space is
negligible and the redundancy hardly woth worrying about, what database are
you going to use ?

Pete Kane


"perspolis" <rezarms@hotmai l.com> wrote in message
news:uQrYf985EH A.2196@TK2MSFTN GP11.phx.gbl...[color=blue]
> thx to all who reply me..
> if tables for Sale and Purchase are big, I think it's better to have them
> seperatedly...i sn't it??
>
> "perspolis" <rezarms@hotmai l.com> wrote in message
> news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=green]
>> hi
>> I have two table named Purchase and Sale..all of fields of
>> both tables are the same...I make them design in one table
>> with an additional boolean field to determine which is Sale and[/color]
> Purchase...[color=green]
>> it is good to design them in seperate tables without additional field??
>> or design both in one table...
>>
>>
>>[/color]
>
>[/color]

[Nick Malik [Microsoft]]

Re: Database design question?

Hello perspolis,

No. Database size is not normally a factor in design. With tables like
Purchase and Sale, I strongly doubt that you will ever have the size of
table that would drive you away to denormalize your design in this way. In
a data warehouse I worked on, we had a single table that contained 20
Billion rows. SQL Server 2000 didn't even flinch. (some operations took a
while :-)

Do not make your decision on the basis of expected size. If the business
has different Meanings for these two tables, and considers them to be
entirely different and unrelated things, then you are better off in
different tables because the business may decide to add fields to one that
are not in the other, or create a complex indexing scheme for only one of
the two tables.

On the other hand, if the business expects to move an item through "stages"
where it is a purchase until an event occurs, and then it is a sale, then
use one table. Otherwise, you will have a much more difficult time
answering basic questions like "has customer X been involved in the purchase
or sale of item Y" and "provide the total of all purchases and sales for
category Z"

I do not know your system. The word "purchase" says "accounts payable"
while the word "sale" says "accounts receivable" to me. If that's what
these mean to you too (or the inventory equivalents), then you definitely
want two different tables. The reason being this: when you purchase
something, you purchase it from someone. When you sell something, you sell
it to someone. These "someone" entities need to have some form of
uniqueness to them. Otherwise, your reporting becomes very difficult to do.

--- Nick


--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster

http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"perspolis" <rezarms@hotmai l.com> wrote in message
news:uQrYf985EH A.2196@TK2MSFTN GP11.phx.gbl...[color=blue]
> thx to all who reply me..
> if tables for Sale and Purchase are big, I think it's better to have them
> seperatedly...i sn't it??
>
> "perspolis" <rezarms@hotmai l.com> wrote in message
> news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=green]
> > hi
> > I have two table named Purchase and Sale..all of fields of
> > both tables are the same...I make them design in one table
> > with an additional boolean field to determine which is Sale and[/color]
> Purchase...[color=green]
> > it is good to design them in seperate tables without additional field??
> > or design both in one table...
> >
> >
> >[/color]
>
>[/color]

[perspolis]

Re: Database design question?

I'm using Sql Server 2000 ..
I think for huge information it's better to have them seperatedly..be cause
there is no nedd to get addtional field to know that's Sale or Purchase..
but in my situation fields of Sale and Purchase are the same..

"Pete Kane" <pk_fox@ntlworl d.com> wrote in message
news:7Scyd.151$ u3.123@newsfe4-gui.ntli.net...[color=blue]
> I would always go the two table route, the difference in storage space is
> negligible and the redundancy hardly woth worrying about, what database[/color]
are[color=blue]
> you going to use ?
>
> Pete Kane
>
>
> "perspolis" <rezarms@hotmai l.com> wrote in message
> news:uQrYf985EH A.2196@TK2MSFTN GP11.phx.gbl...[color=green]
> > thx to all who reply me..
> > if tables for Sale and Purchase are big, I think it's better to have[/color][/color]
them[color=blue][color=green]
> > seperatedly...i sn't it??
> >
> > "perspolis" <rezarms@hotmai l.com> wrote in message
> > news:OE7RKuw5EH A.3124@TK2MSFTN GP11.phx.gbl...[color=darkred]
> >> hi
> >> I have two table named Purchase and Sale..all of fields of
> >> both tables are the same...I make them design in one table
> >> with an additional boolean field to determine which is Sale and[/color]
> > Purchase...[color=darkred]
> >> it is good to design them in seperate tables without additional field??
> >> or design both in one table...
> >>
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]