Reputation
7,245
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
1 9 23
Newest
 Explainer
Impact
~171k people reached

1d
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
24
comment Multiple rows v/s multiple columns
Agreed (+1) but you should state your reasons for preferring this approach.
May
24
comment Does schema normalization enhances query efficiency
@FokwaBest - There is no one factor that has 100% influence over query efficiency. If your schema doesn't permit an efficient query because it is badly structured, then you obviously won't have one. Lack of normalization is not the only (or necessarily the most important) way in which your schema could inhibit query efficiency. Inappropriate indexing is more important, for example. If your schema isn't an obstacle but you write your query in an inefficient way then of course your query will be inefficient. What you have, how you structure it and how you access it all come into play.
May
19
comment How should I model an “either/or” relationship?
@DanielSerodio - I used Visio with smart shapes I built myself based on the James Martin ERD notation. The shapes use a custom line texture to give them an informal appearance, which I find helpful in reminding people when a diagram is a "sketch" or draft design.
May
5
comment T-SQL insert value based on identity column
@Jyrkka - I mean a situation in which a relation is not in second normal form. In your case specifically, a customer is uniquely identified by the meaningless sequence number. This number is a candidate key. Adding a customer type prefix to this number does not make it more unique.
May
1
comment T-SQL insert value based on identity column
@Jyrkka - I'm not offended, so don't worry on that account. My point is that using an identifier prefix to encode something that is an independent fact about the entity is considered bad design as it violates the spirit of database normalization rules insofar as it creates a situation in which non-key attributes are not dependent on the whole key. Perhaps pseudo-random is a less apt term than meaningless. An IDENTITY value (or Sequence value) is not related semantically to other (non-key) attributes of the entity. In that sense it may as well be a random number, rather than sequential.
Apr
28
comment T-SQL insert value based on identity column
It doesn't make much sense to me to embed a customer type code in a pseudo-randomly generated customer identifier. The conventional wisdom is not to build meaning into identifiers. As rules of thumb go that is a good one in my experience. However, your mileage may vary.
Apr
27
comment T-SQL insert value based on identity column
@PaulWhite - obviously, but if the IDENTITY is being used in a PK, one would assume that such a precaution will be taken. The OP's question didn't include DDL, so one is left to presume.
Apr
9
comment DISTINCT Role in SELECT
I'm guessing that you have 3,074 agents and they each match your various criteria at least once.
Mar
30
comment How to insert values in junction table for many to many relationships?
@siddhesh - @@IDENTITY is SQL Server. I have corrected this answer to reflect MySQL, which is one of the OP's tags. The MySQL equivalent of @@IDENTITY is LAST_INSERT_ID().
Mar
18
comment database design question - How to make the table 3NF and have no orphans
+1 for mapping close to reality.
Feb
17
comment School fees database design
All financial databases should begin with a double-entry accounting design. Students are customers and will have accounts. Fees are like products that students buy. Find a good reference design for accounting and product sales and apply it to your system.
Jan
31
comment Product Attribute System While Avoiding EAV
I've discussed using EAV for product catalogs on Stack Overflow here: stackoverflow.com/questions/11779252/… - EAV is evil, except when it is actually the best approach. Attributes of products in a catalog is one where EAV is often the most efficient and suitable design.
Jan
27
comment Multiple user roles with different attributes
Your sections are connected, but your diagramming tool doesn't see it because you didn't define the referential integrity in your DDL (or your modeling tool) properly. Note that every specialty role has a key of user_id. This should be FK to Users.id. In other words, Users is a super-type and admins, employees, customers, etc. are all subtypes related 1:0,1 to the super-type.
Jan
27
comment linking a single table (comment) to multiple tables (article,link,attachment)?
This question was essentially asked yesterday, see: dba.stackexchange.com/questions/90137/… The linked question is about sharing addresses, but the same discussion applies to sharing comments, links, etc. etc.
Jan
19
comment Design: how to store lots of booleans?
@AleksandrMakov - A "pure intersection" is a table containing only foreign keys that together make a composite primary key for the table. OBJECT_HAS_THING would be a pure intersection if the only columns in that table are my_object_id and ownable_thing_id and if both of these columns together are the primary key of the table. If you added a third column to this table, say has_one (bit) then it would no longer be a pure intersection.