467,074 Members | 918 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

Database design question?

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...

Nov 16 '05 #1
  • viewed: 1543
Share:
7 Replies
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" <re*****@hotmail.com>
wrote:
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...


Nov 16 '05 #2
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" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...

Nov 16 '05 #3
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" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...

Nov 16 '05 #4
thx to all who reply me..
if tables for Sale and Purchase are big, I think it's better to have them
seperatedly...isn't it??

"perspolis" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...

Nov 16 '05 #5
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" <re*****@hotmail.com> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
thx to all who reply me..
if tables for Sale and Purchase are big, I think it's better to have them
seperatedly...isn't it??

"perspolis" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...


Nov 16 '05 #6
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" <re*****@hotmail.com> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
thx to all who reply me..
if tables for Sale and Purchase are big, I think it's better to have them
seperatedly...isn't it??

"perspolis" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...


Nov 16 '05 #7
I'm using Sql Server 2000 ..
I think for huge information it's better to have them seperatedly..because
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****@ntlworld.com> wrote in message
news:7S**************@newsfe4-gui.ntli.net...
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" <re*****@hotmail.com> wrote in message
news:uQ**************@TK2MSFTNGP11.phx.gbl...
thx to all who reply me..
if tables for Sale and Purchase are big, I think it's better to have them seperatedly...isn't it??

"perspolis" <re*****@hotmail.com> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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...



Nov 16 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Lefevre, Steven | last post: by
2 posts views Thread by Josh McFarlane | last post: by
5 posts views Thread by Don Vaillancourt | last post: by
12 posts views Thread by nyathancha@hotmail.com | last post: by
4 posts views Thread by dgleeson3@eircom.net | last post: by
reply views Thread by David | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.