Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Please critique my ERD.

    My name is Darren and I am a new member here. I am joining in an effort to learn more about creating proper database structures. Needless to say, my skill level is fairly low.

    I am requesting critiques of a ERD that I have created with the RISE Editor. The model is for a delivery service. Any and all advice is greatly appreciated.

    Please click here to see a .jpg of the ERD.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two things seriously amiss

    first, the diagram is missing the PKs and FKs, the sine qua non of relationship diagrams

    second, do not stuff the table name into the column name as a prefix -- that just increases the noise, to the detriment of the signal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    I don't see any way to specify PK and FK attributes with the RISE Editor. I suppose I will have to stick it in Illustrator to add those graphics.

    I wanted all the column names to be unique is the reason why I prefixed them with the table name. I know there is a reason for making all column names unique but it escapes me at the moment. I read the reason just recently but I just can't remember. I will developing with SQL Server 2008 Express Edition R2 and using Visual Studio 2008 Standard. The database will be used for a website created with ASP.NET.

    Thank you for your advice.

    Checking out your book, by the way.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Magian View Post
    I know there is a reason for making all column names unique but it escapes me at the moment. I read the reason just recently but I just can't remember.
    if you do remember where you read it, i would like very much to know

    i've been designing databases for several decades and i've never run across a good reason
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    I don't doubt you one bit. I actually did not do this on the last website that I did. However, I know I read it just very recently. Hopefully I will pull it back out of my head and post it here soon.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    There is no reason for making all the column names unique. Find out where you read that, and then throw that book away.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    I'll have to go back and check the documentation, but I swear I believe it was related to one of the ASP.NET controls. One the vanilla jobs that does a lot of the code-behind for you. If I confirm this I will post back here.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    One time I decided to be "cute" and named all PK's as ID. Then, when I started naming FK's, I got stuck because without having to specify what PK this particular FK references, - I couldn't come up with a reasonable and consistent naming convention.

    Recently I stumbled over a tool that generates a database from an XML file (I think it's called Exult for SQL), where my initial naming standard was used, but with a clever alteration: all PK's are called PKEY, but prefixed with the table name, and then all FK's are called the same as the referenced PKEY, and P is replaced with F. And everything is very clear. Just by looking at the list of columns you can immediately spot the keys, and the table name the the key is referencing.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov View Post
    ... but with a clever alteration: all PK's are called PKEY, but prefixed with the table name, and then all FK's are called the same as the referenced PKEY, and P is replaced with F.
    okay, that's a great scheme, it is very much like my scheme, with a small modification

    change the PK names replacing PKEY with ID, but keep the table prefix

    then change the FKs to match the PKs, and where they say FKEY, replace that with ID

    now take the table prefix off the PK ID

    Quote Originally Posted by rdjabarov View Post
    And everything is very clear. Just by looking at the list of columns you can immediately spot the keys, and the table name the the key is referencing.
    exactly!! that's the same with my scheme!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2010
    Posts
    8
    Quote Originally Posted by rdjabarov View Post
    Just by looking at the list of columns you can immediately spot the keys, and the table name the the key is referencing.
    Thank you for the tip.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Magian View Post
    I'll have to go back and check the documentation, but I swear I believe it was related to one of the ASP.NET controls. One the vanilla jobs that does a lot of the code-behind for you. If I confirm this I will post back here.
    Never let your front-end tools dictate your database implementation.

    Quote Originally Posted by rdjabarov View Post
    all PK's are called PKEY, but prefixed with the table name, and then all FK's are called the same as the referenced PKEY, and P is replaced with F. And everything is very clear. Just by looking at the list of columns you can immediately spot the keys, and the table name the the key is referencing.
    Personally, I HATE that implementation. If I have a single attribute in two locations, (once as PKey and once as FKey), it should have the same name in both places. Not "PSomething" in one table and "FSomething" in another.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think this might be an ADO issue.

    It is possible to return a result set from an RDBMS where there are two or more columns have the same names. If you then attempt to refer to the column via an ADO object by name then it will, quite reasonably, throw an error. As such, the documentation might have been "always ensure the column names of your result sets are unique" (good practice) rather than every column in your database unique (questionable practice).

    For a period I used a fairly substantial third party product that assigned a four letter abbreviation to every table name and prefixed every column name with the respective abbreviation. Despite it being a highly maligned practice I actually found it beneficial.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rdjabarov View Post
    all PK's are called PKEY, but prefixed with the table name, and then all FK's are called the same as the referenced PKEY, and P is replaced with F. And everything is very clear. Just by looking at the list of columns you can immediately spot the keys, and the table name the the key is referencing.
    Does it prefix alternate keys too?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Aug 2010
    Posts
    3
    Quote Originally Posted by Magian View Post
    My name is Darren and I am a new member here. I am joining in an effort to learn more about creating proper database structures. Needless to say, my skill level is fairly low.

    I am requesting critiques of a ERD that I have created with the RISE Editor. The model is for a delivery service. Any and all advice is greatly appreciated.

    Please click here to see a .jpg of the ERD.
    Hi Darren,
    Modeling your delivery service in the RISE Editor sounds like an excellent choice to me. Besides being an excellent tool for modeling your solution, it gives you the opportunity to generate the database layer as well as the application layer.

    First of all, since we are working with an ERD and not a database diagram, I do not agree that the diagram should contain the PKs and FKs. The crow feet clearly identify the relations in the ERD. However, if you would generate your relational database from RISE using one of the available code generators, PKs and FKs will automatically be generated for you based on your relations and the default or your custom settings. Relational databases are of course common target environments when working with a tool like the RISE Editor, but by no means the only.

    When creating your model there are a few things you should think about. When working in RISE you should, as often as possible, work with the stereotypes and common relations, especially if your target environment is a relational database.

    For example: the one-to-one relation between the entities Truck and Truck Size isn’t possible to implement in a relational database, or at least, you will not be able to enter any data into the tables since both sides of the relation require the other side to exist first. You have created a “Cats 22”. The same goes for the one-to-one or many relation between Truck and Driver.

    In the first case, if you intend to create a relational database, you should probably move the Truck Size attributes to the Truck entity, and in the second example you should probably use the one-to-zero one or many relation. For the relation between Truck and Insurance you should probably use the one-to-zero or one relation and so on.

    I also agree that you should remove the entity names from the attributes. There is no point in this in a technical perspective. However, if for some reason, you still want the table names as part of the column names in your database, you can alter the naming conventions in the RISE Editor so that it pads the column names with the table names when generating the database, but I would not recommend it.

    If you want to use an application code generator to generate an application layer for your delivery system, you must add interfaces to your model, you can read more about how this is done in the article Interface Modeling.

    Happy Modeling

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by holsen32 View Post
    You have created a “Cats 22”.
    i completely agree with your point, that a true 1-to-1 relationship is impossible because you cannot enter values on both sides simultaneously (and therefore it has to be a 1-to-0-or-1 relationship)

    i would just like to mention that this is called "catch 22" not "cats 22" -- it is named after an idea in a book with that actual title, "catch 22"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •