multiple tables

phplost

Let me start out by saying I'm a newbie and I'm finding PHP confusing and to be honest I haven't really found that many tutorials around that help,plenty of scripts,but not many complete walk through tutorials.

I'm running win98,PHP4,Apache,and MySQL.

I need some constructive criticism on my table design as I have never done this before and it is for a relational database (I believe thats correct).Anyway it's a project to get myself started and I'm hoping the forum will help me along the way.It is based on bricks for sale that have been left over from projects (as I am a bricklayer).

Heres my table design :

MEMBERS TABLE
id Name Password Email Address Brickid
01 Joe hggfbm joe@ 4 street 1

BRICK TABLE
id BrickName ColorCode Quanity
01 Balmoral 0973 31

BUY/SELL TABLE
Memberid Brickid Buy Sell Quantity Holding
01 01 0.05 0.09 25 06

I dont see any way of cross referencing beyond what I have but if a member buys 10 bricks I have to be able to :

Check to make sure he has the funds first.If he has an offer to buy I need to be able to check that there is still enough Funds left to cover another purchase (I'll need another column for "FUNDS" in the MEMBER TABLE).
Tranfer Funds from 01 to 02 and transfer bricks from 02 to 01.
If 01 only buys 10 out of 25 bricks I need to be able to adjust the quantity of bricks still for sale.
I need to check through the color code and brick name columns to see if anyone else has the same bricks for sale e.g 2 people selling 25 bricks each and one person wanting to by 40.I believe these would be grouped in the Buy/Sell table anyway

I'm guessing that once I get into this more it will become less complicated but I believe I'm correct in my thinking in that it all begins with the correct table structure.

In the brick table the (in BrickName column) Balmoral would tend to come up repeatedly with different color codes,would I need another table : BrickName ??

Thanx to all in advance,even if you only read my post.

[deleted]

So, simply put, you have the following:

members, who have funds, and possibly bricks for sale.

bricks, with a colorcode and a name

sales with a seller, a buyer, a brick-code, and a price per brick.

The problems start at the members table, because one member can have any number of different bricks for sale.
That means we'll need an extra table to hold only the 'bricks in stock'.
Each record in the stock table will contain the id of the member, the id of the brick that is in stock, the number of bricks in stock, and the price per brick.

The sales are also a problem because one sale can contain several types of bricks, from several sellers. It too must be split up into a table for sales, and a table for 'bricks sold'.
The bricks-sold table would contain a sale-id, a buyer-id, a seller-id, a bricktype, number of bricks, and a price per brick.

have a look at this drawing to explain it a bit more:
http://www.hvt-automation.nl/yapf/bricks.gif

phplost

Thanks Vincent,I've read a lot of your posts and understand you to be a very knowledgeable programmer.

THankyou for the diagram,I'll have to give it some study now.

I'm really stuggling with programs being able to take from here and give to there while taking from there and giving to here,or more to the point how it is programmed so that the last action isn't forgotten.I'm looking forward to getting into it as maths was my favourite subject at school and programming is far removed from bricklaying,but its a lot to get your head around and the only tutorials I could find only contained 1 table,a good working example (multiple tables)would do wonders.

Thanks again.

[deleted]

Mind if I take your brickselling app to create such a tutorial?

phplost

I dont mind at all,then it would give me something to compare my work and thinking against.

I have just finished doing a search at MySQL in the hope I missed something in the past but the only thing the search turned up was a reference to Kevin Yanks book or his tutorial which is a plug for his book.The tutorial stops at chapter 4 of the book.

Being from the bricklaying industry I wouldn't think it would be a good business plan and that was never my intention,but as an exercise I thought it was a great challenge for myself.

I believe it was your site I visited a few nites ago that is being designed for the newbie in simple terms,correct ?? If a site existed that had a tutorial that showed how to build a complete site including images, links,tables and using even just 2 tables (but preferably 3 minimum) I think it would become the most popular PHP site on the net.

Scripts are great and there are plenty of them (half of them have mistakes that a newbie cant figure how to fix),but a script is of little value to a newbie,I dont even know how to use the script,getting your head around one script reading another etc for a newbie is not that obvious.

This is a link to the very best tutorial I have found,and I've looked for well over 100 hours.Something like this but using multiple tables would go down extremely well,I would recommend it everywhere I went.Check it out and read the comments at the bottom: http://www.weberdev.com/ViewArticle.php3?ArticleID=22

Please keep me informed,I for one would love to see your tutorial and thanks for the help.

I'm busy trying to figure out your table design right now.

[deleted]

Part one of the tutorial is just about finished.
Have a look and let me know if there are any things that need explaining:

http://www.hvt-automation.nl/yapf/index.php?cmd=viewitem&itemid=21

phplost

Vincent,amazing,brilliant,exceptional,how do I describe it.

A work of art.

Could I suggest either putting a link at the beginning to that other tutorial or creating a beginners tutorial yourself with just the one table (better than sending visitors to another site) with a suggestion that they do a simple one table example first that covers the newbie stuff (from the very beginning) as that other tutorial did.Its just without first doing that other tutorial I would have found myself in over my head as soon as you started talking about SQL and physical model.

I cant see that there will be a site or programmer that is truly interested in helping newbies that wont be making referrals to this tutorial.With this tutorial and a beginners tutorial you will save many a beginner much frustration.I have spent months (true,since February)trying to get my head around PHP and I'm sure many newbies have gone to all the trouble of downloading PHP,Apache and MySQL just to give up in frustration and move on to learning JavaScript or something instead.

I hope you will set up that beginners tutorial with the instructions for downloading and configuring etc,through to a simple one table example and put a forum on your site and I'll come live at YAPF.You will have to change your site name to :TOTPF (The Only True PHP FAQ).Please,not a forum where you have to click on every post and/or cant reply more than once to one post.

The only small amount of confusion I would have at this stage is between :
Brickid...it refers to the primary key,a number,the name,if it were cars it would be Ford,Chev,Honda etc, correct ? It fact it refers to ALL the columns under bricks.

BrickType....refers to the color code,with cars it would be the model Ford Mustang,Ford Thinderbird etc.....correct ?

I love this line :
For the bricks we'll need an id, a name, a colorcode and what ever else you may find interesting about a brick.

After being in the brick business for more than 25 years, what is interesting about a brick.They have to class as one of the most uninteresting things around.

Vincent I cant say enough,I read a lot of your posts prior to joining PHP Builder and I know you to be a wizard,please dont stop moving in the direction you are now,you will save many a newbie from giving up and from many hours frustration.

Once I have got an understanding of PHP I would like to contribute a tutorial covering the basics of html as an introduction to PHP for your site if you think it would be appropiate.

I retreat bowing in respect to the grand master....Regards Kelvyn

phplost

Vincent,I've figured out bricktype,just a bit sleepy (sorry)

Could you explain why "price" in saledetail is historical ??

Am I correct in thinking that "price" in the stock table is present time,as in offers to buy/sell and that is why "price" in saledetail table is historical.Is it from the programming perspective so that once the transaction in table "Stock" is done you then have a reference to complete the transaction through table "salesdetail",hence it being historical.

Hope I'm not jumping into lesson 2 before I finish lesson one here.

I'm new,I'm allowed to ask stupid questions

Regards Kelvyn

[deleted]

lol

yes the price in the stock is the current price, and the price in the salesdetail is the price at the time of the sale.

salesdetail will keep a complete record of all sales.

And that is not just for fun, it is also a 'must' due to MySQL's lack of proper transaction control. But that is lesson-2 material, in the writing as we type....