Before I Go Too Far

[blyxx86]

I've contemplated doing this for a while, but as I was writing the CREATE TABLE syntax I became a little weary about it.

Essentially I am creating one table for both INBOUND and OUTBOUND products, with a ENUM('IN','OUT') separating the two. I was going to create two views vinbound and voutbound.

My question is... Does this violate any database principles? I am trying to stay within BCNF and having multiple TrackingNumber fields (one in an Outbound and one in an Inbound) seems to violate BCNF?

I'd like your 2 cents before I hit execute on my script.

Thanks!
Kyle

[code green]

Instinct is screaming that the table should be split into two.
This is the way it would be done in big set-ups.
I have never seen sales and purchases mixed.
Say in the future you wished to compare inbound products to outbound products.
Quite a simple query with two tables but with one you would JOIN a table to itself giving each an alias and yourself a headache.
This of course is only my opinion from personal experience.

[blyxx86]

That's how I used to look at it too, but I remember someone suggesting that (logically) they are related to each other and should therefore be placed in the same table.

However, I have been arguing this in my head for a while about whether they are actually separate things, or if they are in fact related to each other enough to have in the same table.

Essentially.. The tables would be identical, and it would seem like the concepts of database normalization are being broken. BUT!!!! Are they really being broken? One is showing SHIPMENTS, the other RECEIPTS. The views I was creating broke the table into two separate entities which I would be using through the application instead of directly back to the table.

My instincts are the same as yours, but there is something about having two (well with the child entities four) duplicate tables that just doesn't feel right either.

[code green]

I can see you point. Both tables would have the same or similar field headings
Product, Date, etc.
But a 'sale' generates an invoice, a 'buy' a purchase order.
Similar but different
My sales tables have an order_date, packed_date, shipped_date.
But purchase tables have order_date, date_promised and date_received.
Similar but different

Sometimes with normalisation it is sometimes better to settle for a happy medium.
For example, more tables mean more joins but leaner tables.

I prefer to build flexibility into a design and allow for future changes
but rarely take normalisation to its limit.

[blyxx86]

I'm currently taking a database processing class and normalization was about 6 chapters worth. It was pretty intense, and it got engraved in my mind.

I built a database a while ago that had the two table version. It works just fine, it was just the discussion I had with someone that made me think the tables are related because they have the same root 'noun' of "movement."

I am opting to go for the two table version. It just makes more sense. One could argue that it breaks normalization rules, but it could also be argued that it doesn't. I'm riding the fence right now between the two. I really just don't know.

I appreciate the second opinion on this. I needed the little push to get me off the fence and on to a side.