macOS SQL SELECT question

[ChrisA]

You need to read up on DBMS Design theory. Specifically
what they call "Database Normalization". Not understanding
the basics of DBMS theory is likely to result in painting yourself
into a corner. DBMS system have a way of starting out simple and then growing. Starting with a fully normalized design means that you can
grow the application without needing to start over.

Use Google to find "Database Normalization"
but one refference is
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Your suggestion amounts to a kind of "anti-normalization". About the last thing you'd want to do.

Do not be afraid of doing Joins. the Join operation is the whole reason
we use _relational_ databases, because they enable Joins.

One "Red Flag" in any DBMS design is when you find that your code needs to make one query, hold a result from it and use that result to make a second query where the result of the second querry is really what you want. A design such as that will break due to concurrentcy or if you try to use locks it will not scale and could evn rsult in deadlocks unless yu understand theory related to deadlock prevention and detection.

The second "Red Flag" is any design that stores the same information twice
Like spliting the table as suggested.

OK Put all the above into use:

You need to make several tables
(1) A Parts table. It will have one row for each part. The primary key is "part_number"
(2) An "Order Table" it has one row for each order. Primary ket is "order number"
(3) An Oder Detail table. This has two colums "order number" and "part number"

The above design is a "clasic" used in many text book examples

You can extend it to include a "customer" table and an "saleman" table
and so on.

A good "rule of thumb" in DBMS design, if you don't want to bother with formal normalization theory is to make a table row represend ONE (and one ONE) object or event. for example a prt or a transaction or a person. You table does not do this. It describbes a part AND when it was ordered. So it is not surprize that the only way to querry the info you anted was to join to subsets of the table together.
.

I guess I could split the PartOrders table into HistoricalPartOrders and LatestPartOrders, and do INSERTs into HistoricalPartOrders and UPDATEs into LatestPartOrders. But that sounds like it's have a bunch of tradeoffs, and I'd have to do a query from both to get all the data out.

Anyways, I still have to try out everyone's suggestions (had to fix some other code first).

 

[devman]

I was reading that kind of thing in my SQL book, about reducing redundancy in a given table, by splitting things into separate tables. So, where I had PartOrders, you have Orders + OrderedParts (I already had Parts). One drawback is that it looks like the SQL queries would be harder to come up with, since every query would involve some kind of joining.

Yes - that's precisely the point. Seriously, this is database 101. Go get a good book on relational database and start learning. In general - things are much more difficult if the data is not normalised.

edit: ah my oops. didn't see there was a page 2 and hence ChrisA above said this better than I.

 

[ChrisA]

Originally Posted by MarkCollette
I was reading that kind of thing in my SQL book, about reducing redundancy in a given table, by splitting things into separate tables. So, where I had PartOrders, you have Orders + OrderedParts (I already had Parts). One drawback is that it looks like the SQL queries would be harder to come up with, since every query would involve some kind of joining.
.

OK so it take a join. This is not an expensive operation. May time the DBMS can work with the index files and the result is very fast. The query optimizer is quite good.

The thing about data bases is that thier usfullness grows over time. As son as you decide to keep inventory into it then yu start thinging about putting a workstion in the wharehouse so "stuff can be loggin in and out and then the sale staff wants access and the execs want reports maed from it and then you decide to link it with a customer table and before long youhave 50 people depending on it every day.

Now you can see why you wil so nuts if you don't design it right from day one. Eventually you will be forced to normalized the data and going back and changing after numerous apps depend on it is very painfull.

Also if you do go and read some of the more advanced DBMS books, mostly university level textbooks, you can work though a serious of theroms and proofs (using some advanced algabra) where they prove that if the DBMS has certain charicteristics that it is possable to extract _any_ specifiable set of information. That's a very powerful thing: Amathmatical proof that you have not painted yourself into a corner.

Relational database system where for years a mathmatical construct studied by computer scientists and people though you could never actually build one that it would remain just something like a "turring machine" that is used to advance the mathmatics but never actually built. And then Stonebreaker (sp??) wrote Ingres. and 30 or so years later here we are.