Reputation
7,360
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
1 11 24
Newest
 Yearling
Impact
~187k people reached

2d
awarded  Yearling
Aug
16
comment Storing resource information efficiently
Is this really a big problem? It seems to me the most complicated part is working out the display of items stored as date ranges, which you've obviously done already. For splitting/recombining existing records when changes are made, it seems to me you just have to find all of the overlapping records and either update them or delete them, depending on whether they overlap or are fully contained within the range being "updated" by the user. See stackoverflow.com/questions/20177304/… for how to find date range overlaps.
Aug
14
comment Normalization for postal addresses in US (address, county, city, state, zipcode)?
There is no point in normalizing ZIP codes. Normalization is for data integrity, not for lookup efficiency. There are no facts that are important to an address that are fully functionally dependent on a ZIP code, therefore there is no reason to create a table with ZIP code as its primary key. An index on ZIP is more than adequate for making retrieval by ZIP Code quick.
Aug
14
comment Normalization for postal addresses in US (address, county, city, state, zipcode)?
City and ZIP are many-to-many. Some ZIP codes cover huge areas and some cover a single door.
Aug
3
awarded  Constituent
Jul
28
awarded  Caucus
Jul
10
comment Product attribute schema design, mixing solutions
JSON array is just EAV stored in a really awkward format. Even relational EAV is better than a JSON array.
Jul
5
comment Snooping-safe Vote tables
Ranvijay, I agree with the direction you're suggesting. If you expand your answer to give more detail, perhaps an example and discuss how this gives the protection the original poster is asking and also what potential shortcomings your answer might have, then you will get more up-votes and soon you will build enough reputation to be able to participate in the site in more ways.
Jul
2
answered How to decide when is use ERD or UML for data modeling?
Jul
1
comment table structure for events
Google for intersection table and many-to-many relationship.
Jun
27
comment Is it better to use an ID (int) or a Name (varchar) as PK for a table?
I would suggest that using a "name" (any human familiar string) for a primary key is really only safe if the set of values is controlled by an outside standards body that will naturally manage change to be infrequent, such as ISO country or currency codes. In other cases changes and even key conflicts are likely. I know there are three different Joel Browns in my neighbourhood, for example.
Jun
25
comment Many tables v/s one table with different entities types
@hagrawal Thousands of records are not very many records to a relational database management system. Performance is not going to be an issue with data of this size. The only way to tell for sure whether one table or many tables is more efficient is to try each approach under heavy production loads and then measure the resource usage. I would guess that such careful measurement is not worthwhile given the very likely small differences in performance between the two approaches.
Jun
25
comment Many tables v/s one table with different entities types
@hagrawal Writing your code to select and unpack a list of translations based on OR or IN clauses is not necessarily going to be demonstrably more efficient than using a single, simpler query repeatedly. If there is a large latency between your web server and your database server, then perhaps round trips would be an issue, but otherwise my personal preference would always be for simpler code as it is more maintainable and therefore more economical in the long run.
Jun
25
comment Multiple rows v/s multiple columns
@hagrawal - Please beware: Using dynamic SQL is precisely when you need to be concerned about SQL injection. It all depends on exactly how you do it. You need to make sure anything that comes from the user, including things spoofed by malicious browser emulation are escaped to avoid SQL injection. Parameterized SQL queries are much less prone than dynamic SQL to this type of attack.
Jun
25
answered Many tables v/s one table with different entities types
Jun
25
answered Multiple rows v/s multiple columns
Jun
24
comment Multiple rows v/s multiple columns
Agreed (+1) but you should state your reasons for preferring this approach.
Jun
10
answered Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?
Jun
6
revised Best practice for entry position indicators
Grammar!
May
29
revised Trigger Performance - 1 or 2 Triggers?
Spelling and detail in the question title