MySQL database for my coins
FishtailApple12
Posts: 25 ✭
Does anyone know how to use mySQL? Im in way over my head but I’m curious to see if this will successfully manage my coins. Im pretty sure MySQL is for database admins that work for global companies but I think i should use it for coins so if anyone has tips let me know.
0
Comments
I would imagine the overwhelmingly most popular ways to track coins for folks on this forum would be to use an Excel spreadsheet, the PCGS registry or good ol' paper.
In honor of the memory of Cpl. Michael E. Thompson
But excel is boring
Yeah, excel would probably be the better choice. Unless you want to spend a lot of time learning programming and database development. I developed a .NET application that reads/writes to a SQL Server database for this, but I also have almost 20 years experience writing applications and developing data models.
@scotty4449 i want the challenge, I just don’t know where to start.
Just FYI, when building an application there is "front-end" work, like building a user interface to input/output data, and there is "back-end" work, like building a data model to store your data. MySQL would be the "back-end" part of the application.
Yes I know
https://www.amazon.com/VISUAL-NET-MYSQL-Database-Oriented-Applications-ebook/dp/B08HYG81N5/ref=sr_1_16?crid=36QY33XZGFECC&keywords=application+mysql&qid=1660010329&sprefix=application+mysql,aps,125&sr=8-16
Best way to learn is to read a book like the one above. Also, youtube videos and google are your friend.
I've done programming with mySQL and SQL but it has been a while since my peak programming days. Both are used on the backend of websites. For SQL, it would be a .NET site and for mySQL you would most likely be using a PHP website. So, in addition to mySQL, it would be helpful to know web programming and PHP. Get some books and/or let Google be your friend and start your journey down a path of a career change. Lol. Good luck.
Looking for Top Pop Mercury Dime Varieties & High Grade Mercury Dime Toners.
MySQL is freeware, so it is often used by beginners for simple stuff.
I have used it, plus other DBs like PostgreSQL and MonetDB.
I prefer Excel as well for maintaining various lists of coins.
You could use both.
You could enter the data in Excel, then Save As a CSV file, then import that into MySQL.
If you are looking for a place to start, you can learn SQL for free at:
https://www.w3schools.com/sql/
(It's what I did).
Normally, DBs are good for tables with a very large number of rows,
and you just want to locate a few of them.
It's also useful if you have multiple related tables.
For coins, you could have an inventory of all denominations in a single table.
I would recommend a "purchase_date" column.
I'm not sure what you would do with SQL, except maybe to add up how much you have spent?
You could break that down by denomination using GROUP BY in SQL.
Sqlite is even easier to use for small data sets, which fits you.
https://www.sqlitetutorial.net/
I developed commercial software for 37 years. Spare yourself a lot of headaches and stick with Excel. Trust me!
Dave
Dude! How big is your coin collection that you need a relational database to keep track of it? That's impressive...
Successful BST transactions with: SilverEagles92; Ahrensdad; Smitty; GregHansen; Lablade; Mercury10c; copperflopper; whatsup; KISHU1; scrapman1077, crispy, canadanz, smallchange, robkool, Mission16, ranshdow, ibzman350, Fallguy, Collectorcoins, SurfinxHI, jwitten, Walkerguy21D, dsessom.
After trying most every software program that has came out over the last 30 computer years, I always come back to Excel. It is so much more powerful than just a spreadsheet. Information galore may be stored within each cubicle with just a mouseover or a click to include photos of your coins. As many as you have space for. No limit to the number of columns or rows. Here's a sample.
Jim
When a man who is honestly mistaken hears the truth, he will either quit being mistaken or cease to be honest....Abraham Lincoln
Patriotism is supporting your country all the time, and your government when it deserves it.....Mark Twain
You may wish to look into Microsoft Access. It would be a powerful front end tool to meet your needs.
Access would require a little education to go along with, whereas Excel is a little more user friendly and tons of easy tutorials available unless you have a good friend to help with learning the basics and specialities of Access. Just an opinion.
Jim
When a man who is honestly mistaken hears the truth, he will either quit being mistaken or cease to be honest....Abraham Lincoln
Patriotism is supporting your country all the time, and your government when it deserves it.....Mark Twain
I agree that Excel is by far the best approach for something like a coin collection. I mentioned Access since the OP indicated that he wanted to work with a relational database and Access would allow him to create the joins without actually writing SQL.
I am a data architect for my career, unless you want to track millions or billions of records, a SQL DB is overkill for sure. As someone mentioned above, if you really want to learn databases, maybe start with Access, it has an easy to use interface that requires very little programming knowledge, but when you are ready for more advanced coding you can write SQL queries and VBA scripts for automation. You can also build input forms and reports, and it can easily be migrated to a SQL DB later. That said, Excel would be the tool of choice for me, and I have access to all the big players in data today, SQL, MySQL, snowflake, azure, mongo DB, and analytical tools like Python, QlikSense, PowerBI, Tableau, and many other tools.
That said if you are dead set on SQL, take some free online training, or there are great paid options too, I use DataCamp through my employer
@Ikenefic > @lkenefic said:
It's really not that big I'm just bored... I have nothing better to do so why not attempt to manage a database that massive corporations use to store countless records
I had no idea that excel could do that! I thought it was just a spreadsheet that simply had rows and columns.
I'm going to try that out, and if all else fails I will go back to excel.
If you learn VBA, Excel can do almost anything you can program. I have developed several full blown applications based in excel. If you build that SQL skillset it's highly valued and a marketable skill!
Yes, VBA is definitely a benefit, but is not necessary to build your database, just easier once learned. Not sure what you really have in mind, but like all software it is a tool for specific purposes and not toys. All of these software programs by microsoft and adobe are very deep in their programming and can do far more than the average user even has an idea of.
Good luck.
Jim
When a man who is honestly mistaken hears the truth, he will either quit being mistaken or cease to be honest....Abraham Lincoln
Patriotism is supporting your country all the time, and your government when it deserves it.....Mark Twain
I'm not really sure what I have in mind either, I just want to test out everything and see what works best for me. I think everyone has convinced me to use excel, but where do I start? I'm looking to show an alphanumeric id that I have made myself, the name of the coin, the year, mint mark, comments (grade, anything else I notice about the coin), where and when I received it, and its value.
Where to start?
Create a new Excel file.
In the first row, type the column names that you want, and make them bold.
Enter one coin per row.
It's also good to use View / Freeze Panes / Freeze Top Row,
so that you can always see the column names.
I use Excel. One workbook with multiple tabs for each set. I keep track of dates, grade, cost, variety, and a notes field too. I've started to hyperlink image files too... very user friendly. IMHO...
Successful BST transactions with: SilverEagles92; Ahrensdad; Smitty; GregHansen; Lablade; Mercury10c; copperflopper; whatsup; KISHU1; scrapman1077, crispy, canadanz, smallchange, robkool, Mission16, ranshdow, ibzman350, Fallguy, Collectorcoins, SurfinxHI, jwitten, Walkerguy21D, dsessom.
I wrote the whole SSDC Registry in perl and MySQL. It is very easy to work with, powerful, and has a short learning curve. Yes, it is natural for maintaining a catalogue of collections and more. I do everything on command lines and with vi editors. Most people would probably be more comfortable using a GUI interface and it probably has fine tools for producing reports. I really cannot stand GUIs and code builders. I went with portability and open free for the project, which is why it is also hosted on Ubuntu servers. But it comes down to how you want to use your data. Spreadsheets have their limitations but your use might not require more than that.
My preference is Excel, which I have used in the past. Plus, my wife teaches it at college level, so if I have a question or get stuck... Voila'... answered or fixed
Cheers, RickO
Yeah Excel is the way to go. And if cost is an issue you could use the always very good Open Office which is free and community supported. Many of the features that were available only in relational databases such as data validation via keys etc. are available in Excel and other spreadsheets now, for example you can pick a coin denomination from a dropdown in Excel vs. typing it in each time etc. Plus you don't need to learn SQL to retrieve your data. I have spent over 3 decades dealing with relational databases starting with Borland Paradox so could design and code a coin collection database in my sleep but prefer Excel for it.
Whatever your choice good luck and have fun! Always good to learn new things...
K
This is an earlier iteration of an Excel spreadsheet I came up with. It's pretty simple to add columns... I've added a couple extra... one for hyperlinks to image files... Enjoy!
Successful BST transactions with: SilverEagles92; Ahrensdad; Smitty; GregHansen; Lablade; Mercury10c; copperflopper; whatsup; KISHU1; scrapman1077, crispy, canadanz, smallchange, robkool, Mission16, ranshdow, ibzman350, Fallguy, Collectorcoins, SurfinxHI, jwitten, Walkerguy21D, dsessom.
Agreed. I've used MS Access to design a database for my coin inventory, and write queries from the data to summarize, extract, etc. etc. It helps out a lot.
I have settled for excel, and it’s basically working how i want it to. I even did some formulas to add all the components of the coin into a long name which I’m very proud of.
I’m not sure why I went with sql to start, it was very overkill. Anyway, thanks for all the suggestions guys!