Writing a mySQL database from the ground up

Question

I recently got an internship involving the writing of an sql database and don't really know where to start.

The database will hold the information of a ton of electrical products: including but not limited to watt usage, brand, year, color, size, country of origin. The database's main task is to use some formulas using the above information to output several energy-related things. I will also be building a simple gui for it. The database will be accessed solely on Windows computers by no more than 100 people, possibly around the area of 20.

The organization does not normally give internships, and especially not to programmers (they're all basically electrical engineers); I got it via really insisting to a relative that works there who was looking into how to organize some of the products they overlook. In other words, I can't really ask THEM for guidance on the matter since they're not programmers, which is the reason I headed here to get a feel of where I'm starting.

I digress -- my main concerns are:

1. What is some recommended reading or viewing for this? Tips, tricks?
2. How do I set up a server for this? What hardware/bandwidth/etc. do I require?
3. How do I set up the Database?
4. For the gui client, I decided to take a look at having it be a window showing a webpage built with the sql embeded into php. Is this a good idea? Recommended reading for doing that? What are alternatives?
5. What security measures would you recommend? Recommended reading?

I have: several versions of Microsoft's mySQL servers, classroom experience with mySQl and PHP, several versions of Visual Studio, access to old PCs for testing (up to and including switching operating systems, hardware, etc.), access to a fairly powerful PC (non-modifiable), unlimited bandwidth.

Any help would be appreciated, thanks guys!

Answer 1

1. What is some recommended reading or viewing for this? Tips, tricks?

   I'd recommend spending quite a bit of time in the design stage, before you even touch a computer. Just grab some scrap paper and a pencil and start sketching out various "screens" that your UI might expose at various stages (from menus to inputs and outputs); show them to your target users and see if your understanding of the application fits with the functionality they expect/require; consider when, where, how and why they will access and use the application; refine your design.

   You will then have a (vague) functional specification, from which you will be able to answer some of the further questions posed below so that you can start researching and identifying the technical specification: at this stage you may settle upon a particular architecture (web-based?), or certain tools and technologies (PHP and MySQL?). You can then identify further resources (tutorials?) to help progress toward implementation.

2. How do I set up a server for this? What hardware/bandwidth/etc. do I require?

   Other than the number of users, your post gives very little indication of likely server load from which this question can be answered.

   How much data will the database store ("a ton of electrical products" is pretty vague)? What operations will need to be performed ("use some formulas ... to output several energy-related things" is pretty vague)? What different classes of user will there be and what activities will they perform? How often will those activities write data to and read data from the database (e.g. write 10KiB, once a month; and read 10GiB, thousands of times per second)? Whilst you anticipate 20 users, will they all be active simultaneously, or will there typically only be one or two at any given time? How critical is the application (in terms of the reliability/performance required)?

   Perhaps, for now, just install MySQL and see how you fare?

3. How do I set up the Database?

   As in, how should you design the schema? This will depend upon the operations that you intend to perform. However, a good starting point might be a table of products:

   CREATE TABLE products (
     product_id  SERIAL,
     power       INT UNSIGNED COMMENT 'watt usage',
     brand       VARCHAR(255),
     year        INT UNSIGNED,
     color       VARCHAR(15),
     size        INT UNSIGNED,
     origin      CHAR(2) COMMENT 'ISO 3166-1 country code'
   );
   

   Depending upon your requirements, you may then wish to create further tables and establish relationships between them.

4. For the gui client, I decided to take a look at having it be a window showing a webpage built with the sql embeded into php. Is this a good idea? Recommended reading for doing that? What are alternatives?

   A web-based PHP application is certainly one option, for which you will find a ton of helpful free resources (tutorials, tools, libraries, frameworks, etc.) online. It also is highly portable (as virtually every device has a browser which will be able to interact with your application, albeit that ensuring smooth cross-browser compatibility and good cross-device user experience can be a bit painful).

   There are countless alternatives, using virtually any/every combination of languages, runtime environments and architectures that you could care to mention: from Java servlets to native Windows applications, from iOS apps to everything in between, the choice is limitless. However, the best advice is probably to stick to that with which you are already most comfortable/familiar (provided that it can meet the functional requirements of the application).

5. What security measures would you recommend? Recommended reading?

   This is another pretty open-ended question. If you are developing a web-app, I'd at very least make yourself aware of (how to defend against) SQL injection, XSS attacks and techniques for managing user account security. Each of these areas alone are quite large topics—and that's before one even begins to consider the security of the hosting platform or physical environment.