Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Edward

Mysql Solution

Recommended Posts

I want to ask a question, say you needed to store a quantity of an item on your website 0 to 100 for example. But you could mark that this item as always in stock. Would it be a viable solution to set the quantity column in your MySQL database to NULL if you wanted to mark that something was always in stock. Or should we make a seperate ENUM column to mark stock limited or unlimited, so you would then require two columns. I have already had many situations where null values were appropriate so how about this one?

Share this post


Link to post
Share on other sites

I would say there's no definite answer here, but your solution sounds good. An alternative is to give the items a specific status code, like -1. That way, it's easier to see that it's explicitly set. Null is totally fine though. There's some pitfalls using null (invalid input data = null) but I don't think it should give you any real problems.

  • Upvote 1

Share this post


Link to post
Share on other sites

Thanks for getting back to me on that, yes i think Null will do fine then i am consistently checking for null values in many of my situations through the website so its something i am used to working with now. Its just seems to me a waste of time to make another database column when you have have one column do all the work. Thanks for your help Antonio.

Share this post


Link to post
Share on other sites

True that. It's often a nice way to not over complicate stuff. I use null values a lot myself.

 

Just make sure no values are set to null unexpectedly. Say a query is ruined by bad input, and suddenly you have "Shoes" at the column's value. I'm pretty sure that'll default to null on an integer column. Just keep that in mind, as it can be hard to debug. That's my only inclination to use an unusual value like -1.

 

What are you building here btw? :)

Share this post


Link to post
Share on other sites

Yeah that's a good point i didn't think of that, this is the thing i guess you have to get things up and running and see what kind of bugs come into play and rebuild version 2. I'll have a rethink on this one!

 

I am building another website similar to ebay there are are quite a few of these now, they are becoming more like different banks or grocery stores. Well i know i won't be able to win ebay, i just want to get my foot in the door and do my own thing, some things will differ but to be honest what goes on will be quite much the same.

 

You know this website i can't finish i just have to get it to a level where it can go online. Its really becoming a lot more work than i expected, i have had to change databases, go back and recode parts of the site. I mean my code has even got better the way i am thinking, i am using Yii now more than i was to begin with. Its really all a learning process for me i am learning something new every day as i guess we all are.

 

I remember when you told me before don't worry about swear words you can do that stuff later, well i know what you mean now, its better just to get the overall website built, then brush up on those small things later on, that's what i am doing more now. I am working on a tough part now, i had to plan the database 3 times before i got it right, and recode 3 times, it looked easy but later i found reasons why i couldn't do what i wanted to do.

 

Another thing i used quite a big extension for Yii, well later on i decided to pull this out completely as it was no longer maintained and something better came along, so i had to spend about 2 weeks going through the whole site changing the code. Seriously if there is another extension like that again, i will really think if its a good idea or not to use it.

 

Well i seem to be on the right tracks now, so there is an update if you want to know how i have been.

Share this post


Link to post
Share on other sites

Sounds good! Looking forward to seeing the result.

 

DBs take a while to settle usually. Functionality is added to the site, you do some changes here and there, another use-case requires a restructuring, things don't perform well enough... The list goes on. Changes has to be made accordingly and it can be a pain in the butt.

 

Things always take way longer than expected too. Think the task will take one week? How about two! It's also hard to anticipate what you'll struggle with and it's often the one thing you expected to be a piece of cake. Weird, huh. :P

 

Well. Anyways. Good luck! Sounds like a fun project.

  • Upvote 1

Share this post


Link to post
Share on other sites

You know i am really not sure about this situation...

 

I think i am going to add in an extra column for now because if something goes wrong with one column it will be more trouble to fix later as from what i can see now. If for some reason one column is the way to go with a NULL value for unlimited stock then i will change it.

 

quantity ENUM('limited','unlimited');

quantity INT UNSIGNED

 

Larry any suggestions on this situation? What is the right thing to do? I have the same situation with shippings either having a cost or being free.

Share this post


Link to post
Share on other sites

It's definitely an important point. I'll let Larry give his input on the matter, but you know my thoughts.

 

The null value issue can be solved by using the signed integers as codes and not allowing nulls. This is what I've done in similar cases. Also If you want to do any programmatically with the stock value, I would also go for a TINYINT as boolean rather than textual enums. If you only need them for textual stuff I see no problem.

 

Anyway. I know you got this. Just my two cents based on recent experiences.

Share this post


Link to post
Share on other sites

I thought of using integers when i started off but when you are testing it would be hard to remember what all the numbers ment especially with my website its hard to know even where I am now. I'll probably keep enums and change them in restructuring if it every so happens as it helps me to find the bugs considering i have over 30 databases now.

 

So its seems an extra column seems like the way to go? You could also use an unsigned column for stock quantity but if some had a 0 quantity and somehow it ended up at -1 (your unlimited quantity number) then we would have problems again.

Share this post


Link to post
Share on other sites

True that considering your hypothetical case. You'd need to build stock availability checks into your code, but you should do that no matter what. The code remembering issue is fixed by adding constants/an options array to your class. Product::UNLIMITED_STOCK, etc. Preventing such bugs is a valid reason for sticking to enums though.

 

Another column is a good solution, so stick to that if that seems most comfortable. It's not anything bad with such a solution.

Share this post


Link to post
Share on other sites

Yes the constants if you are using the numbers for a class those would be fine, i actually did that in the beginning then decided to go with enum's. Enum's are very useful for debugging in the database with the precise data. Well it looks like we seem to be answering our own questions unless Larry has something to say on this matter.

 

I though HartleySan had experience with MySQL or am i wrong, may be just PHP and JS.

Share this post


Link to post
Share on other sites

I don't have strong feelings about this, but I'd be inclined to use -1 to indicate unlimited quantity. I don't like NULL, because that has other implications. And I don't like using a second column, because this can be represented by one column. Again, no strong feelings here, but that's how I'd be inclined to play it.

Share this post


Link to post
Share on other sites

Can you explain why NULL has other implications being what exactly? It matters what you say as i have many tables.

 

Okay i will revise my databases.

Share this post


Link to post
Share on other sites

Well, NULL technically means "no known value". Semantically, it means something different than -1 or whatever. Of course, -1 doesn't mean "infinite" in terms of product quantity either. 

 

My main recommendation here would be to think about how the different options would affect your queries. If the quantity could be an integer or NULL, what does that do to your queries? If it could be any integer, but -1 means "always available", what does that do? And what do both results do to your PHP code that handles the results?

  • Upvote 1

Share this post


Link to post
Share on other sites

I see what your saying, usually i check the value first to see what it is before adding it to a query, this was for my situations. However i am a beginner so i could be making mistakes. I will keep what you said in mind for future code.

Share this post


Link to post
Share on other sites

Well today its time to go back, i need to fix my databases i need to take out the extra columns and go with the -1 for unlimited or free rather than holding a value for the cost. The funny thing is i did think of the -1 idea before but i didn't use it for two reason i was scared that someone's 0 quantity could become -1 which wouldn't look good on the website, the other reason was because the attribute would be using a INT SIGNED which would be bigger than INT UNSIGNED. I am laughing now because i think i could of done with a smaller integer anyway so it probably wouldn't of mattered. I was going to review this stuff when the website was complete.

 

Anyway thanks Larry and Antonio i will go back now to make the changes to the databases where i have the problem.

 

ISNULL i was looking at in your books is also very useful but i will try to avoid nulls in money columns and use some kind of default for safety.

 

I  don't know btw Larry if you got the Star Wars Death Star but i have some cool Christmas presents coming. I will take a picture and send you by email so you can take a look.

  • Upvote 1

Share this post


Link to post
Share on other sites

One other note since this is a decimal column the value here is now -1.00 that is for cost if its free. I hope this works out the same. Ignore this i could just set it as 0.00 for free lol how stupid of me.

 

Anyway i got all this working now so its done. One way or another i will get it all done.

Share this post


Link to post
Share on other sites

Glad you're making progress. And thanks for continuing to share. 

 

Haven't picked up the Lego Star Wars Death Star yet. Still in my mind, though. Maybe I'll treat myself for my birthday in February. I'm in the process of selling a bunch of vintage Star Wars figures, so I could take that money and buy something I can actually play with!

Share this post


Link to post
Share on other sites

I hope you will get it soon. I wish i still had some vintage Star Wars action figures. Are they still sealed do they have boxes. May be if you can't sell them i would be interested to have a look at them? You can send pictures to my email.

Share this post


Link to post
Share on other sites

These are all original Star Wars action figures, unopened. My in-laws had the foresight to buy them and never open them. They gave them to me a few years ago. They've been sitting in a box, because I'm not a collector. This summer I thought about them again, realized what they're worth, and said "I've got to get these out of my house!" So I've had them all professionally graded and am selling them on eBay. Not having any problems selling them! ;)

Share this post


Link to post
Share on other sites

I've just been checking out ebay today i see they have them going for $1000 - $5000 each. I used to have that AT - AT my Dad took it somewhere to sell with other old stuff we needed to get rid off, someone saw the Star Wars stuff and bought them all for very cheap, my we were unaware of the actual value.

 

I am still waiting for your email... Would be interested to have a look at the Star Wars figues you have left.

Share this post


Link to post
Share on other sites

Yeah...it's a whole rabbit hole I've gone done with this Star Wars stuff. On the card is worth WAY more than loose. Unpunched is worth way more (and is pretty rare) than punched. And then there's a whole grading system. So far I've sold 15 of them for like $4000, but I've spent $2500. And it's taken more time than I'd prefer. But I'd rather get them to people that value them more than I. I've got 4 more left that I'm trying to sell before Christmas, and then I'm done with it.

 

Will hopefully send that email today. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...