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

Aug
12
comment Handling or preventing conflicts in a multi-user system
+1 for optimistic concurrency. Note that if you happen to be using SQL Server, there is a native data type called rowversion which automatically assigns a new value each time the record is written to. This same data type was previously known as timestamp, but that syntax is deprecated.
Aug
9
revised How to link a customer to a country when the city/state isn't known
Clarified language.
Aug
9
revised how to explain indexes
Clarified the "problem" being explained.
Aug
9
answered how to explain indexes
Aug
9
answered How to link a customer to a country when the city/state isn't known
Aug
1
answered What is a standard or conventional name for a column representing display order?
Jul
4
answered UNF to 3NF is this relation workable?
Jul
1
comment Data modelling: RBAC for small enterprise software
Mike81 - Changing the primary key of employments to the combination of users_id and organizations_id doesn't change the relationships in any way. The employments table is still a many-to-many intersection table, so each user can belong to many organizations and the role assignments for employees are still organization-specific. It would mean propagating user and organization columns to your role_assignments table, which would allow direct joins for convenience. Your FK constraint would from role_assignments would still be to employments though.
Jul
1
comment What's a good way to model user authorization to hierarchical data?
Joe Celko describes the visitation number approach to managing hierarchical data as nested sets in his book SQL for Smarties which is an excellent resource.
Jul
1
comment Data modelling: RBAC for small enterprise software
You don't state your exact requirements, so it is difficult to critique your model. This model will work fine if all you need is to handle one or more role assignments per user where these roles can be in one or more organizations. It is flexible insofar as it permits different role assignments for each organization. Some people would question the need for employments.id since this table is a pure intersection and you could just as easily use a compound primary key for employments. Some might point out that this could have performance benefits when checking user roles.
Jun
29
answered Implementing a Record Keeper/Rollback System for a MySQL Database
Jun
23
answered Can a database detect that data is out of sequence if each datapoint has a sequential ID
Jun
14
answered Auto-increment key and Foreign Key as composite for relational database
Jun
9
answered User roles and permissions
Jun
4
comment Am I wrong in table design or wrong in selected index when made the table?
@achy - Your solution shows that the lack of a selective index on stat_valid and mark_as_trash is the culprit in your performance issue. I would assume you were getting a table scan because of this, which can really slow things down, especially if it's done too soon in the execution plan.
Jun
4
answered Am I wrong in table design or wrong in selected index when made the table?
Jun
3
answered financial transactions with and without commissions
May
27
answered Record versioning and promotion
May
21
awarded  Constituent
May
20
comment Database design: Two 1 to many relationships to the same table
@dendini - You solution number 2 doesn't fit with any of the solutions I outlined. This is because it doesn't fit with the requirement of an account belonging to one legal entity. The way you've defined the primary keys of the intermediate tables, they are many-to-many intersections. If the primary keys were just corporation_id and person_id then you would essentially have the sub-typing solution, except that the super-type table would have been split into two and the foreign key will have been inverted, so people couldn't hold multiple accounts. This kind of defeats the purpose.