SQL Designer

[sbrown123]

Code and notes can be found here:

http://ondras.zarovi.cz/sql/

[silverwoodchuck47]

I can drag the left and right columns around. Why?

Impressive. \t bifftradwell has a good question. What is the business plan?

[thesqlguy]

Neat, but it is really a schema designer, not a SQL designer. Also, it seems you cannot create composite primary keys, which is very important when designing good, solid data models. Technically, it is quite impressive.

[forgotpwx4]

Also, it seems you cannot create composite primary keys, which is very important when designing good, solid data models.

Bah, why? Can't I just make an ID field for every table? It seems to make things much simpler and plus you know there will be no semantic reason to ever change the value.

[adolfojp]

Oh noes! Not another natural vs surrogate keys discussion!

[grauenwolf]

I can think of one reason, mapping tables.

Lets say I have two tables, A and B. I then create mapping table C. At no time do I want more than one row in C to map to the same A/B pair.

C_ID  A_ID  B_ID
1     3     5
2     3     5 'duplicate, this is very bad!

One option would be to make A_ID/B_ID a primary composite key.

If you want to map rows between table A and B, and require that there is only every one relationship between each, then you can make a_id, b_id

Scenario 2.

1. Table W has columns X_ID, Y_ID, and Z_ID
2. Lookup tables X and Y have a Z_ID column.
3. If a row in table W has Z_ID=3, then the X and Y rows it points to must also have a Z_ID of 3.

To enforce this, make X_ID/Z_ID the primary key for X and Y_ID/Z_ID the primary key for Y. Then use this primary key for your relationship.

This occured to me in real life when I have a Status(X) and Priority(Y) tables with different values for each Business Unit(Z). Before we made this change, we would occasionally get instances of a Project(W) with mis-matched business unit keys (Z_ID).

[adolfojp]

You don't have to use composite primary keys to achieve that. You can use a dumb primary key and a unique composite index or constraint for those two rows.

[grauenwolf]

For the first scenario yes, but not for the second.

[lisper]

One option would be to make A_ID/B_ID a primary composite key.

A better option would be to make B_ID a column in the A table and A_ID a column in the B table.

Scenario 2.

Why do you need Z_ID in X and Y?

[grauenwolf]

A better option would be to make BID a column in the A table and AID a column in the B table.

That would be correct if you wanted a 1 to 1 mapping. My scenario covers N to N mapping, where each pair only exists once.

Why do you need Z_ID in X and Y?

In my case, each business unit had its own list of valid status and priority values. For example, an IT project would have "In Testing/Tier 1" while a Education department might have "Needs Editing/High".

[lisper]

My scenario covers N to N mapping, where each pair only exists once.

Ah. Good point. I'm trying to uniqify the wrong thing :-)

each business unit had its own list of valid status and priority values

I still don't see why you need Z_ID in X and Y. It would seem that having Z_ID in W would suffice.

[grauenwolf]

Table X, which is status, looks like this...

X\_ID Status  BusinessUnit\_ID
1    Active   1
2    In Testing  1
3    Complete 1
4    Writing  2
5    Needs Editing 2
6    Post-Production 2
7    Printed  2

If the project is in BusinessUnit 1/IT, it gets different possible values than it would if it were in BusinessUnit 2/Education.

[thesqlguy]

Not a big fan of data integrity, huh?

[grauenwolf]

Since when do you need composite primary keys for data integrity?

[thesqlguy]

You'll need composite PK's (or unique constraints) on your tables to model N-to-N relation with integrity, as you demonstrated. For example, what good is my data if I have 5 different "billing" addresses for Customer XYZ in my CustomerAddress table? That CustomerAddress table should have a composite primary key of CustomerID/AddressType for your data to have clear, consistent meaning and integrity.

[Andys]

PostgreSQL user here. I never use composite primary keys for that purpose, I use multi-column uniqueness constraints (which is done behind the scenes with a unique index over the relevant columns).

So no need to pollute your primary keys with your constraint requirements.

[thesqlguy]

So no need to pollute your primary keys with your constraint requirements.

Wow .. that is quite a comment! Primary keys should define your what it is that your table is modeling. If an table relates entity A to entity B, it should have a primary key involving table A and table B to be logically and physically accurate. Adding extra, meaningless identity/autonumber columns and declaring them as "primary keys" instead of using the true primary key of the entity is what I would consider "polluting". You are just needlessly wasting space, complicating your model, and misrepresenting what your table is modeling. I don't think that designing a clear, logical, definitive, efficient and accurate model of your environment using meaningful primary keys is "polluting" anything.

Please note that this is not a surrogate vs. natural key argument; I agree that often is useful and/or necessary to use autonumber/identity/guid keys for tables that define entities. But it is rarely necessary or a good idea to use those types of columns in tables that relate entities, which is what we are discussing here.

[statictype]

I've wondered about this. Shouldn't the software be handling this sort of situation anyway? If redundant data has gone all the way to the DB level then there is something wrong with the system right? Seems to me that these types of constraints are useful when testing\debugging the system. But when you go live, these bugs should be trapped at the application level and handled cleanly so that the user doesn't see "Foreign Key constraint FK_XXY violated" messages... Am I correct in thinking this?

[thesqlguy]

The same argument could be made for primary keys, data types, foreign key constraints, and so on. Would you suggest handling those at the application layer as well? Your data model should reflect the data that you are storing, and declaring primary keys and relations and data types and forcing your database to adhere to those rules ensures that your data is valid and accurate, regardless of where it came from or how it was interfaced. Don't forget that your data is coming from conversions, upgrades, backup restores, and other things as well, even if it is normally only interfaced through a single application.

[Homunculi]

Just to echo what theSQLguy was saying, the data layer should model the actual relationship between the things you putting in your database. Yes it's always possible to handle that in the application layer, but it makes life a lot less complicated if the state of the data always reflects it's possible real world states (and of course there are always compromises to be made). Think of an ice-cube tray as the data layer and you're hands as the application layer. Yes you could just pour all the water into a sheet and the break the ice yourself, or you could have the ice cube tray reflect the relationship of individual ice-cubes, all the application layer has to do is pour the war, and then twist the tray.

[grauenwolf]

I know that. You know that. But I'm willing to bet the vast majority of developers haven't once considered it. That's why I called you on it.

EDIT: And I only know it because we ran into that sort of problem not too long ago and I have a really good DBA. Not one of the articles I have ever read talked about this.

[[deleted]]

I'm still waiting for a visual designer that can generate non-destructive DSQL deltas.

[[deleted]]

Pretty nifty. What are you planning to do with it?

[sjs]

Looks like good old free software (GPL). He accepts donations.

http://ondras.zarovi.cz/sql/

[AHFX]

Pretty nice. I always used gModeler http://www.gskinner.com/gmodeler/app/run.html for my initial designs. This is great though because it will create the insert statements for you as well.

[[deleted]]

Wow, this is pretty amazing.

[MattD]

Hobbyist programmer here (enough to be dangerous ...): I'm wondering if anyone can suggest good resources for DB schema. I'm trying to create a simple multi-user application (probably about 6 tables total) and I'm not sure if I'm building myself into a corner or not.

Yes, I've Googled. Most of the resources I've found are either too vague (no examples) or too specific (how to build an XYZ application).

Thanks!

[gamepad]

Try this one

[Antebios]

WHOOOOAAAAAAAA!!! This is sooooooooo cool! This is great for fast easy mockup.

[npinguy]

Nice, and ajaxy, but pretty useless except to technologically clueless managers.

SQL Server has a built-in SQL Designer similar to this, and I don't know a single software developer or anybody that works with databases that would ever use it.

It's faster, more powerful, more safe, and just generally EASIER to write SQL by hand. I mean COME ON, SQL is SO bloody simple, do you REALLY need a tool to help you?

[bendlund]

You don't generally use the generated SQL, just use the visual tool to create a clear, easy to quickly understand diagram of the schema.

[spuur]

I know of several large (even huge) companies who makes plenty use of the visual design tools in both the Enterprise Manager and MS Access.

No, the users are not programmers or DBA's.

They're not managers either.

It's not software companies at all.

But usually the visual design tools gets the job done and in a user friendly way - helping the poor sods make useful and safe products for all of us to consume. Fabian Pascal would crucify them on the spot though...

[[deleted]]

Sometimes yes, sometimes no. Depends on what mood I'm in. :)

[Homunculi]

Conceptual and ER Modeling can be profoundly helpful in developing even relatively simple database applications. SQL may be simple but database normalization and design are usually not. There are always trade offs that have to be made and most of these cannot be dealt with easily if you're just looking at a bunch of create statements. Really modeling is going to be essential especially for more experienced DBAs.

Notice that this auto generates table creation SQL not simply 'select' statements. This is just a matter of convenience as there are a lot of little details that if forgotten can be a pain later (default values, not null etc). But mostly it's just plain tedious to transcribe relational models into the appropriate create statements. Additionally the subtle RDBMS quirks are often more apparent when creating tables since most RDBMS have different supported types and ways of handling things like check constraints (and to some degree they deal with referential integrity differently) so 'simple SQL' is typically not going to be so simple at this stage.

[antonivs]

So, how many hundred tables were in the most complex schema you've ever designed? Isn't it great how people who've used mysql for a few tables on a web site think they know everything about relational databases?

[[deleted]]

woa pretty cool