Tag Info

Hot answers tagged

8

Attempt to fetch the user record based on the username (but use LIKE instead of = so that case doesn't matter. Usernames should not be case sensitive. If you managed to fetch a user record (then the user exists) and you can compare the passwords to validate the login. About password security Store the passwords using a function like password_hash() so ...


7

SQL scripts are the right way to go: The information was originally created via a sql statements, most reliable repeat-ability would come from the same script Import/export can also get tricky if two devs are modifying the same table. It's hard to code review an export file. It's hard to debug an export file We have 5 database environments in our world: ...


6

If you only care about direct reports, you do not need to use recursion. Every employee (except the CEO) reports to exactly one manager. If you want to know direction and indirect reports, you need recursion. This would answer the question "who are all the reports of this director, including managers, supervisors, and peons." It might not be at the same ...


6

While a high number of columns is a common denormalization smell, one can have a three-column table which is not normal and a 100-column table being completely normal. I see column names in plural, which tend to be worse smells (do Issues and HobbiesAndInterests contain comma-separated numbers or some evil thing?). There's also some columns which look like ...


4

As you figured out by yourself, SQL is the way to go, but if you put the SQL commands into scripts or embed them in code does actually not make a big difference. What matters is that you make the upgrade process robust. Therefore, I would recommend to consider the following improvement over track your changes in sequential sql or code files Instead, ...


2

Updated answer There are benefits and drawbacks to adding a new table, and to storing it on the Restaurant table. Benefits to putting it directly on the restaurant table is that you get all your Restaurant info in a single read. However if this is a large row with lots of data you may not want to update it all the time. Putting it on a secondary "stats" ...


2

It's not enough to list the fields in the tables, you also need to look at the values inside those field. You will see this in the first step, which is normalising it into First normal form. Then you can continue to Second normal form, and so on. Usually you only want to normalise your database to Third normal form. Each normal form has criteria that's easy ...


2

If you want to go with the file approach and have mostly static and configuration data use SQLite (PHP entry): it is a RDBMS just like MySQL, but it is a local single file in a directory of your choice (it can even be in your application's folder), installation free, lightweight and can accomplish your data requirements with consistency and a lot of tested ...


2

You could invert the direction of the key: have Table1 and Table2 each contain a column that references back to the Main Table and make these non-nullable. This forces the referential integrity that you want, at the cost of making queries a bit more difficult because you cannot start from the Main Table to query. Not impossible, but something to think about. ...


2

Typically I'd just search the table for the record that contains the matching username. If that record doesn't exist the user doesn't exist so there is no need to go any further. If the record does exist that record would have the password hash on it for that user. Take the password given by the user, hash it in the same way, compare to the hash in your ...


1

I'm trying to determine when a web application should query a database for related data that may or may not be used in the current request. For such general questions it's hard to give a general answer. You have to consider two points: convenience and performance. Many things, which seem at first sight convenient are at hindsight performancekillers. ...


1

In your DAO layer, you should have a way to mark a relationship as required or optional (terms may vary based on whatever framework you use). Required relationships mean that if A links to B and I query A, the web service must return B as well. Use this for data where the two objects are always used together. Optional relationships mean that if A links to ...


1

For the above fields, if any of the above filed has opportunity in any time to have more than one value then that table should be split into multiple tables with one to many relationship. In case if all the fields required single time insertion at all time then no need to split it. This will lead you to third normal form.


1

Just try to break it down into logical tables. Make one table for only contact information, one table for opportunities(i.e. WantsRecoveryCoach, WantsTelephoneRecoverySupport)that way the individual tables are simple enough to understand without needing over-complicated queries to sort the data. It also allows you to separate public information like names ...


1

You can store this whereever you like. I would store it in the Restaurant table but another one for stats might make sense - it depends how often you update it, and whether you update the restaurant entries often or at all. An alternative approach would be to store the average in the business logic that fetches and returns the data. The average is then ...


1

To me, there's no question. Go with Option 1. It makes sense that you'll have Products and Categories. They're different, and shouldn't be forced to live in the same table. However, a productName (reasonably shortened to 'name') is not the same as a categoryName (also reasonably shortened to 'name'). You can use context to figure out which 'name' you ...



Only top voted, non community-wiki answers of a minimum length are eligible