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.

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

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.

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

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.

There is no reason for making all the column names unique. Find out where you read that, and then throw that book away.

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.

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.

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

exactly!! that's the same with my scheme!!

Thank you for the tip.

Never let your front-end tools dictate your database implementation.

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.

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.

pootle flump ur codings are working excelent.

Does it prefix alternate keys too?

pootle flump ur codings are working excelent.

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

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"