6,795 reputation
1923
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years, 4 months
seen 4 hours ago

I'm a professional software developer with more than twenty years of experience across many industries and the entire systems development lifecycle. I'm the principal consultant at Mooseware Limited.

@joelabrown

google

facebook

linkedin


Dec
17
answered database design for storing individual users' items
Dec
6
answered Suitable way to record users requests for usage invoicing/billing in database?
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
revised Advice on table structure change
Reformatted markdown and text to be more readable.
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
13
revised Database Design for Paper Submission System
Added more detailed ERD
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.
Nov
5
comment Database Design for Paper Submission System
@IrfanAliMemon In the ERD with four tables, SUBMISSION records the act of a paper being submitted. Assuming the editing process may sometimes involve rejection(s) and resubmission(s) this is 1:M with PAPER. For each such act of submitting a paper, there may be a different list of AUTHORs - which is M:N with SUBMISSION. This M:N relationship is resolved with the CONTRIBUTION table.
Nov
4
comment How to insert values in junction table for many to many relationships?
@xyz Having implicit COMMIT is not the same thing as never having or wanting to control you transaction scope. When you are writing into multiple tables in a single logical unit of work you will want to control your transactions explicitly
Nov
3
reviewed Approve Are bitemporal tables convenient, compared to valid-time and transaction-time tables separately?
Nov
3
answered Composite Foreign key to single primary key
Nov
2
revised Preventing duplicate data when usual normalization only makes sense for some of the records
Expanded and clarified.
Nov
2
answered Preventing duplicate data when usual normalization only makes sense for some of the records
Nov
2
answered How to insert values in junction table for many to many relationships?
Nov
2
answered Database Design for Paper Submission System
Oct
18
answered Can't finish my ER diagram
Oct
17
comment Why does a UNIQUE constraint allow only one NULL?
Semantically there is a huge difference between Null (don't know) and Null (don't care). Not making a distinction between these in either the standard or the various implementations makes for a lot of resulting confusion. You could say that all "null/don't know" are equal but all "null/don't care" are distinct, for example. It makes for strange, arbitrary implementation decisions by each RDBMS builder.
Oct
17
comment Can't finish my ER diagram
You should go and take a closer look at the ERD reference that you linked. I'm pretty sure you have the crow's foot backwards for what you intend. Your diagram says "a book has one or more publishers" (which may be true in some real world scenarios) but it also says: "a publisher publishes (exactly) one book" which I'm sure you didn't mean. Same thing for members renting and authors writing.