Is there an "If Exists" option in SQL?

[rudeboymcc]

OK I've done some research into data normailisation now, and the relationship table i made before was First normalised form. which is the lowest one.

SO trying to make it third normalised form i get what you see in the attachment.

I haven;t tried to work with it yet, but does it look right?? TO me it looks like a lot of unnecesary stuff going on but if it helps data integrity then i'm all for it.

 

[MagicMan]

An old DBA expression, Normalize till it hurts, denormalize until it works. In a large corporate environment with 187 tables normalized to the average 2nd level, I had the DBA's experiment...they were only able to normalize to the 4th normalization...they really couldn't understand the 5th. The number of tables grew to 652 and not one sql statement could be converted due to the complexity of the 4th normalized state. At the third, we had 315 tables, and more than 30% of our sql statements were too complex. The DBA's rewrote one of the applications, and the process time trippled to perform the same report. Normalization is great, but is often not practical. I fully agree on normalizing to the highest practical level, but sometimes that is level is the first, or second. Most of my projects landed somewhere just above the 2nd. The same can be said for Indexing fields in the tables. When I was with IBM, we advocated using indexes, especially with DB2 when it was first released. Well, the sales jumped and then died as clients found process time was 20 to 50 times that of IMS databases. It wasn't until the impact of many index's became apparent, that DB2/Sql started to receive performance improvements (better index handling,storage, etc) but to this day, the greater the number of indexed fields, the slower your performance. Not to say indexes are bad...they are great and increase performance for functions using the index. They require thought and planning, as does normalization.

Add on: Data integrity .... nasty thing ... normalized example... If I have a product called a widget selling for $1 and some clerk decides that widget is not worth selling and changes it to a wonker selling for $100 mid year...normalized, every widget sold becomes a wonker and if we sold 10 before and 10 after the change and if the price is calcualted (another normalization practice) it would report 20 wonkers sold for $2000 when we sold 10 wonkers for $1000 and 10 widgets for $10....The exec's would be all over you for the missing $990. In other words data integrity failed due to normalization. In a flat file that would not happen. But in a flat file, if we wanted to change the name of widget to wonker and missed one, we would report we had one widget sold which is wrong...arguments can be made but in reality, data integrity is a major component of the application and its rules, audit trails, etc. Normalization when done correctly, reduces the likelihood of errors, and therefore enhances data integrity. Normalization does not provide data integrity.

 

[georgedwilkinson]

An old DBA expression, Normalize till it hurts, denormalize until it works.

I disagree with that sentiment. It is a myth.

In a large corporate environment with 187 tables normalized to the average 2nd level, I had the DBA's experiment...they were only able to normalize to the 4th normalization...they really couldn't understand the 5th.

It takes work to NOT normalize to 5NF. If something is already normalized to 3NF, it is frequently already in HNF. If databases get too large because of normalization, it is because of a bad design, not because of normalization.

 

[MagicMan]

Geroge,
I didn't say the DBA's were good. LOL. They learned a lot in that exercise. And whats wrong with normalizing until it hurts (the highest NF) then stepping it back when you cannot create code to support it?
By the way, do you have a comment on rudeboymcc's design and comment?

 

[Rich]

You don't need the table joining Landlords to Properties each relationship is a One to Many, One Landlord can have Many properties, One Property can hold Many tenants, One Tenant can pay Many rents etc.,etc,
Depending on the complexity of your db and how much info you actually want to collect it may look something like this

 

[rudeboymcc]

Rich, before you said :

You need to normalise your data and structure, Landlords info does not belong in the properties table other than the FK in the same way tenants do not belong in the property table, you have several one to many relationships here

So i took landlorts out fo the properties table by making the Landlords to properties table. but now you say i don't need that, I can't see how else I can take out landlords from the properties table. and i can't really tell if you're saying that it's all good becuase they're all one to many relationships or if it's bad.

Your design looks very complicated, I don't think i'd be able to write any SQL for that. This database is only for 150 properties so i don't think it needs that much normalization. out of interest did you come up with that just now or have you used that design somewhere?

 

[Rich]

Rich, before you said :




Your design looks very complicated, I don't think i'd be able to write any SQL for that. This database is only for 150 properties so i don't think it needs that much normalization. out of interest did you come up with that just now or have you used that design somewhere?

It's a long going project
I'll try and post a much simplified version, the complication comes of course when you realise just what a db can actually do, I assume you just want something very basic, and don't want to store rents collected etc

Basically the Primary Key from Landlords becomes the Foreign key in say Properties, the Primary key from Properties becomes the Foreign Key in Tenants and so on and so forth, follow that structure and you can get who the Landlord is for any tenant by using queries

 

[rudeboymcc]

That's the thing, this is simple for now but i will want to do all the rent monitoring with the same database in the future. I'll have a look at yours in more detail and see how much of it applies to me.

 

[Rich]

Same principle again, One Tenant can have Many payments, the tenant PK becomes the Foreign key in say Payments

 

[MagicMan]

As you can see from Rich's relationship, your first releationship post was not 1NF, but was in fact 3NF (even 4NF...not knowing all the data requirements). The last relationship you posted was 5NF+. The HNF (highest) is what you wish to achieve..ergo your original post. You normalized till it hurt (5NF+) and now you can denormalize to the original.
If you read up on denormalization, it is especially applicable to high trafific online systems and small operating environment (PC's) systems.

 

[Rich]

There is nothing to be gained in this DB by denormalising the structure, without wishing to appear condecending to the OP it would bring more problems than those solved, it's a small business not a giant corporation

 

[MagicMan]

OK I've done some research into data normailisation now, and the relationship table i made before was First normalised form. which is the lowest one.

SO trying to make it third normalised form i get what you see in the attachment.

I haven;t tried to work with it yet, but does it look right?? TO me it looks like a lot of unnecesary stuff going on but if it helps data integrity then i'm all for it.

Rich,
Your schema is great.
I was making a comment in regards to this post ... untitled_1.jpg in the post.
Are you saying, tables Flat Rooms, Properties Flats, Properties Tenants, and Property Landlord should stay in the design? I personally disagree, but I've only been programming since 1972.
For the stage that rudeboymcc is at in development, the original relationship posted on page 2 seemed acceptable, with a few enhancements gleened from your schema.
Bob

 

[Rich]

Rich,
Your schema is great.
I was making a comment in regards to this post ... untitled_1.jpg in the post.
Are you saying, tables Flat Rooms, Properties Flats, Properties Tenants, and Property Landlord should stay in the design? I personally disagree, but I've only been programming since 1972.

I didn't suggest that it was or should remain

 

[MagicMan]

Rich, I tried pushing George, and you to answer the question in that post. That type of connection table has it place at the 5NF for large database structures. I really don't want the impression the last untitled_1.jpg was fine, when it in fact is overkill for rudeboymcc's current development stage and volume.
By the way, thanks for posting such good advice.

 

[Rich]

Rich, I tried pushing George, and you to answer the question in that post. That type of connection table has it place at the 5NF for large database structures. I really don't want the impression the last untitled_1.jpg was fine, when it in fact is overkill for rudeboymcc's current development stage and volume.

I wouldn't argue with any of those sentiments

 

[rudeboymcc]

Hi again, Cheers for all teh help guy but i've decided to change the relationship table again.

I don't like the way before where i had an indicator if the foreign key was an address, flat or room, and certainly don't like having these three fields in every table.

so i came up with the new one (see attached). What do you think@??

 

[MagicMan]

This is probably overly normalized for you, but the concept is what I thought you wanted.

 

[Rich]

This is probably overly normalized for you, but the concept is what I thought you wanted.

It is, way too overnormalised

 

[MagicMan]

Yup, but it may be a better solution, once de-normalized.

 

[Rich]

Yup, but it may be a better solution, once de-normalized.

Why denormalise it at all?