Tag Info

New answers tagged

0

It is a good practice to identify which columns definitely need an index by some upfront analysis. There is a real risk of gradual or unexpected performance degradation in production as database size increases if you have absolutely no indices. The situation you want to avoid is where a commonly run query requires scanning a large number of table rows. It ...


0

Although I don't think every query should be optimized, indexes are so much a part of RDBMS that they need to be given consideration before releasing. When you execute a query, unlike other forms of programming you're not telling the system how to execute it. They develop own plans and almost always base it on the availability of an index. The makeup and ...


4

No, they're not. The purpose of auditing is to determine who did what in the past. The current data is irrelevant for this purpose, but you will need to record which data changed - there's no point in saying "user Dave change the person table" without saying what he changed. This information doesn't have to be recorded so formally though. You might like to ...


1

Keep price range data in the PriceRange table, but store this information in the quotation also, using extra quotation detail tables if necessary. This is no different than any other ordinary invoice. The prices for each line item are always copied to the invoice line items, so that they reflect the price of the items at the time the invoice was created.


0

I assume the issue is that you would like to have some constraints and foreign keys to make the database automagically enforce statements like "every quoted price is within the price range for that customer/product/etc", but can't because the price ranges can change. I would associate a timestamp with every price range and quotation. When the price range ...


1

The users table is definitely the place to store it. Note that if you use MySql (and possibly some others?), you can also use an enum, so that you can be sure that the value that you write is in range, and you can refer it to it legibly in your application's code (e.g., onLine, instead of 1).


3

... table in my database named "users", which has their username/password/mail etc, Of course you mean that you're storing the "hashed" or similarly calculated value derived from the user's chosen password. How do I best store a users status? I would go with an Integer value in the users record, which is a foreign key to a (tiny, little) look-up ...


2

I agree with you, storing the status as integer will be enough. And I would prefer store it in different table, let's say user_status, since it will be updated frequently according to user activities.


2

There is no problem storing big strings or binary data. I worked with a database with more than one terabyte of binary data and worked very well (postgres) and the only bad thing was the backup time. The big question is: "Will you need do constant searches in this text?" If you are going to search for strings in the text, you can think in one index ...


4

No, it is not premature optimization, but it must be done correctly as any optimization should be. Here is what I would do: Load the database with enough test data to mimic a production load. You cannot get this 100% accurate but that is fine: just put enough data in. Does one table have a fixed amount of data? Load it up. Do you have one table that holds ...


0

It also depends on how many users you expect. You should definitely do some load testing and make sure your database can keep up with 10s to 100s to 1000s of simultaneous requests. Again, it depends on how much traffic you expect, and what areas you expect to be used more than others. In general, I would fine tune the areas that I expect the user to hit ...


20

I feel this is premature optimisation because our application is not even released yet. I suggested to monitor for slow queries once we go live and then add indices accordingly. You can't treat your end-users and production environment like quality-assurance. In more words, you're saying that you'll figure it out in production. I don't think that's ...


2

Following proven patterns for known problems (like finding a record by its ID) isn't premature anything. It's just sensible. That said, indexes aren't always a straightforward business. It's often tough to know during the design phase which indexes your traffic will depend on and which will bottleneck write-operations. So, I'd argue for leveraging some ...


3

When your application is released, it is too late. But any proper development process should include performance testing. Use the results of your performance tests to decide which indexes to add, and verify their effectiveness by repeating the performance tests.


24

"Premature optimization", in its derogatory sense, means costly optimization that might not be needed. It doesn't mean all optimization implemented before the latest possible point to prevent bankruptcy! In particular, it's legitimate to optimize based on performance tests before going live, to ensure you can meet some sensible (albeit approximate) ...


48

monitor for slow queries once we go live because nothing says quality like making your users suffer for a lack of design! You should know which queries need indexes when you design the tables, you know which columns are being queried on in where clauses and joins. These should be indexed already because what might not be apparent in a live environment ...


124

Premature optimization is "optimizing" something because of a vague, intuitive sense that, y'know, this will probably be slow, especially to the detriment of code readability and maintainability. It doesn't mean willfully not following well-established good practices regarding performance. Sometimes that's a difficult line to draw, but I'd definitely say ...


0

Since you didn't follow the convention of Eloquent you have to type it manually and it's easy just specify the second and third arguments into the hasOne or other functions of ORM obj. i.e: class User extends Eloquent { public function phone() { return $this->hasOne('Phone','id','phone_id'); } } Where the second argument is the column ...


0

The ideal solution would be for your source application to expose a SOA layer to give external applications access to the data. Failing this (or where the query volume is large and or intensive) then database replication is a good solution -- always assuming the technology is in place to do this reliably. Failing the above two solutions I would go for a ...


0

There are some questions to be asked: Does external data have a timestamp PK or an incremental PK that would allow you to know what were the last data items you already processed? Do you need to process only new data or old data that has been modified also? If the external data has a timestamp PK or incremental PK, the state you need would be the last ...


0

It seems to me like you're putting the cart a bit before the horse, especially since you have a partial implementation already. Take a step back and ask yourself what "all" means and what purpose it serves. In this case it means "without conditions or filters", and the purpose is to provide a way for the user to tell the system "I don't care what the tags ...


1

What you seem to need is an additional table mapping questions to a question group and another mapping question groups to recommendations. Based on that data, you could query the questionnaire and find all question groups that it completely matches. From this, you can derive your recommendations.


-1

Most people in production are painfully forced to keep things simple as the data size grows. There is normal form and non normal form in design and real world is a different animal. So you may not want a five table join in production. So in non normal form, one table. It is important to isolate the data changes so you are not forced to change the structure ...


0

I would add a duplicate() method to invoice, so that the edited invoice is always a copy of the original. If the user cancels editing, the duplicate gets discarded and nothing else happens. If the user submits their changes, then I would compare the edited invoice against the original invoice to figure out what fields should be updated on the database, ...


0

I agree that it's not a relational database. I think the author is simply saying in the quote not to use it as one. Having said that though you may or may not need one. If you don't really need to do much querying on the data, and only intend to store it and then fetch it later based on some limited query criteria then you need XML DOCUMENT storage and ...


0

According to my understanding in the above diagram they have included the two entities/tables instead of one i.e orders and order item so that accessing the information becomes easy when two entities are designed. And order item is dependent on orders entity so it is considered as an weak entity. because the characteristic of weak entity is it depends on ...


2

This doesn't call for a linking entity (although they very useful for N:M relations and infinitely better than trying to cram a list of things into a single field or multiple fields!). What you want is a SONG entity that can be referenced by multiple RECORDING entities. A SONG has a title, artist etc., while a RECORDING might have a bit rate, loudness, ...


1

There are at least two aspects of database design that your question touches on. The first is the issue of class/subclass design. What is the best design for classes and subclasses when designing a relational database? (This issue is also known as types and subtypes or generalization and specialization). The second is the issue of representing a ...


1

I once worked on a system that modeled similar data. Not geographical, but hierarchical, where the hierarchy was not necessarily well-defined and entities might "skip" layers: A -> B -> C was just as valid as A -> C. Using separate tables is great when you can guarantee the data will match the structure, and in fact must match the structure. However, that ...



Top 50 recent answers are included