6,795 reputation
1923
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years, 3 months
seen 5 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


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.
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
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.
Sep
30
comment Supertyping Inventory Transactions
Agreed. Further to this, if you think you really want to run queries against one particular transaction type, create views on the transaction table(s). If the big table with nulls approach is used, the views on the table will almost certainly be writable as well.
Sep
28
comment Database design : hotel room and its detail
Room type and room size on a room description table might violate 3NF if the type and size depend on the room, not on the description of the room. You don't state your assumptions about dependencies, you just split a bunch of properties of rooms off from the rooms table in a way that might be creating transitive dependencies. Your inference of my logic is in exactly the opposite direction.
Sep
28
comment Database design : hotel room and its detail
A table of code tables (your Type 2) is generally considered to be an anti-pattern. You should probably avoid it. If you are concerned about the number of tables in your database you might use something like schemas to allow you to focus on an area of interest while "ignoring" the other areas in your database. Alternatively, you could (and probably should) just come to terms with your aversion to many tables. Any real world business system will have lots of tables.
Sep
28
comment Database design : hotel room and its detail
It looks to me like you are suggesting blending room type and room size? Or are you creating a three tier structure: room -> room_description -> room_type (and other lookup tables, such as room_size)? If the former, that would not be third normal form. If the latter, why introduce a middle tier? What non-key attribute do you expect to depend on a description code?
Sep
6
comment Need Help in DFD Diagram for online hotel booking system
Data Flow Diagrams are well suited to documenting systems where data is stored in files or systems where there is some ETL involved. In a transactional system built on a relational database, your data doesn't "flow" anywhere. The only information in your DFD is actors and actions. What you want is probably more something like a Use Case Diagram, not a Data Flow Diagram.
Aug
30
comment How to store user's language knowledge (150+ languages) in mysql DB
Correlated sub-query is the way to go as far as clarity of code is concerned, but are your sub-queries correlated? Shouldn't it be more like: from dbo.user A and WHERE user_id = A.user_id ?
Aug
23
comment Is one-to-one relationship normalized?
@Gili - re: the cost of joins: There is no right answer to that question aside from "it depends". Join cost is impacted by many factors. Whether they are negligible is even harder to answer, because that is ultimately subjective. The best way to answer you question is to mock up some test data and do volume testing. Try it both ways and see if you can tell the difference using real world data volumes (whatever that implies for your application).
Aug
22
comment Is one-to-one relationship normalized?
@Gili - If your assumption were true then this case wouldn't apply. Segregating large and infrequently needed columns allows more rows to fit on a page, thereby allowing faster retrieval of the commonly used columns. Obviously reading the segregated columns along with the commonly used columns would be slower since a join is necessary.
Aug
1
comment Struggling with normalisation (database design critique)
The way you've pictured it works, as long as clients and sites can potentially have more than one contract and contracts are only ever for one site or one client. You also need to add constraints or logic to enforce that a contract is for either a client or a site. If these cardinality rules aren't what you intend, then you might need to change your design.
Jul
30
comment Conceptual model vs Logical model vs Physical model
What you're referring to on the Wikipedia page is the DDL (data definition language) that defines the physical schema in minute detail. DDL is one way to express the physical model - probably a very good way, since it is very precise. However, it is not the only way. You can express a physical model graphically, for instance.
Jul
28
comment Conceptual model vs Logical model vs Physical model
I don't think you're on the right track, exactly, especially with the conceptual model. See this answer for something which holds up pretty well, I think: stackoverflow.com/questions/4279089/…