Tag Info

New answers tagged

-2

You don't need recursion. Have a look at what is "représentation intervallaire" (sorry, I don't know the word in English) : http://sqlpro.developpez.com/cours/arborescence/ With this you can know all the children without recursion.


0

You can get a free copy of Graph Databases from O'reilly, it is a great read. You have to forget all the RDBMS idioms when you approach using a Graph Database. The relationships are not modeled with a join table, they are modeled directly into the mode with a *relationship". Person -> Tags[Tag] -> Resource Where Tag is a Property.


0

You should have very specific, semantic requests. For instance: You want to pull information about a specific owner example.com/owner/123 You want to pull information about a specific owner's assets example.com/owner/123/assets Now you want to pull all assets that belong to owner 123 example.com/ownerAndAssets/123 Or you want to pull a specific ...


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 ...


5

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 ...


0

Your problem sounds like it is caused by a failure to follow the Interface Segregation Principle. Fortunately, there's a fairly simple solution: take your entity objects and create one interface in each for each type of client, containing only the methods that make sense for that client. Then, for each client, create a wrapper for your ORM that works using ...


0

I agree with @BartvanIngenSchenau : add the validate inside toString. Also: toString would be much better be renamed to build, to more strongly suggest a Builder Pattern. Unlike C#, in PHP the convention is to NOT use the letter I in interface names getters are not necessary maybe mark the validate method as private. From what I now, in the context of the ...


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 ...


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 ...


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, ...


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: ...


0

Certainly not the the approach 2, as it will require an extra overhead of keeping User_Resources and User_Permissions in sync. for eg. an entry in User_Resources would override everything in User_Permissions for a particular resource. I think, solution lies in defining the terms more elaborately. If I think in terms of OOP I dont see how permissions can be ...


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. ...


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.


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 ...


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 ...


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 ...


0

Your problem has a solution on other RDBMS (postgresql/oracle) without creating any table : a materialized view Update it with your scheduled task with refresh materialized view Since you got MySQL you can only simulate a materialized view via a temporary table. : http://www.materialized.info/ or https://dev.mysql.com/doc/refman/5.0/en/create-view.html ...


0

For implicit relationship generation when creating a database-first model in Entity Framework, the tables must have primary keys and reference the other tables' primary keys with foreign keys. If you like having the joins kept inside in the SQL layer (in code, I meant, most times LINQ creates a SQL join in the statement), create a stored procedure and map ...


0

I would not store it at all. What you want to use here is a "VIEW". Not a new table. The view is the result of a query, kind of a virtual table. Last: think hard about your architecture, your solution might not scale well on an RDBMS


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" ...


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 ...


0

Did you consider using some JSON-based noSQL database like MongoDB? It might perhaps fit your needs. And you obviously can put JSON text in some SQL relational database like PostgreSQL, MariaDB (or MySQL) or Sqlite Saving your persistent state as some PHP file is brittle, error-prone (beware of code injection), and probably not very efficient. Several ...


-1

If you ask for my opinion and my advice. Go with MySql because in the previous years I have built application using a JSON data structure but it has been very hard to maintain (it might also be my luck of knowledge). JSON is cool and simple. I admit !! But not as powerful as MySql. Go with MySql. MySql Vs JSON


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

Yes, performance will be impacted by creating a new connection every time - it's not a zero cost operation. However, is this a significant problem for your application? Only you can answer that after having done some profiling. The answer to this is not to use a singleton though - at some point, your app is almost certainly going to want two simultaneous ...



Top 50 recent answers are included