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

21h
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.
Jan
19
comment Design: how to store lots of booleans?
@AleksandrMakov - If one of your major use cases is searching by a list of particular types of things that your object has, then using 300 bit columns will inevitably result in table scans, so your query performance will has tortoise as it were. Depending on how many of your ownable things a typical object has (i.e. if the average number is much less than 300) then you are probably better off using the pure intersection rather than a fully populated intersection with a bit flag. Treat the absence of a link record as "not owned". This will be at least as performant as 300 bit flags.
Jan
19
comment what is better, multiple column or multiple rows while schema design
Third normal form is your friend. You should read up about the normal forms and design your transactional tables around 3NF (or higher, if applicable). Denormalize along the lines of your first table design for reporting purposes, if necessary for performance reasons or for purposes of having a stable snapshot.
Jan
5
comment what is a good use case for splitting user table by role
@user20358 Yes, a view won't save anything in terms of read performance, unless you materialize the view. However, the cost of reading through the view is very likely to be negligible. The greater issue is more likely to be maintainability of your code.
Nov
16
comment Advice on table structure change
Since your scores are all mandatory, there probably isn't a resource-usage argument to be made for splitting them out. You could make a future-proofing argument for splitting scores/comments into an intersection table between BOXSUPERVISION and SCORE_TYPE or the like.
Nov
16
comment When using underscores, should they be reserved to junction tables?
Name your tables so that the names make sense in queries. Use the closest thing to your organization's natural business language that is practical. An intersection between USER and ROLE should be ROLE_MEMBER not USER_ROLE. Sometimes slamming the two referenced table names together with an underscore is the best you can do, but I almost always feel like I've failed if I resort to that.
Nov
13
comment Database Design for Paper Submission System
@IrfanAliMemon - I have posted a complete ERD. Note that primary key columns are underlined and foreign key columns are italicized. (In the CONTRIBUTION table, columns are in both roles.)
Nov
13
comment Database Design for Paper Submission System
You have misapprehended, somewhat, the intention of the CONTRIBUTION table and my repurposing of your SUBMISSION table. Please see my edited answer for clarity.
Nov
12
comment Database Design for Paper Submission System
@IrfanAliMemon The Contribution table is a pure intersection. It would have a primary key that is composed of the combination of the two foreign keys to Submission and Author. There is no need for any other columns in the intersection table. New and revised submissions are implicitly related insofar as they are for the same Paper and therefore share the same foreign key value, e.g. PaperID.