Database

Re: Adding an Identity Later T-SQL

3-Dec-09 22:11

A quick guess, I never script these thing I use SSMS, but don't you need a data type in the alter as well as the create.

ALTER COLUMN [ID] INT IDENTITY(1,1)

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd4-Dec-09 0:49

Vimalsoft(Pty) Ltd

4-Dec-09 0:49

does not work , but i found a resolution. here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7f24ba5a-b091-4119-9216-1564b61f4aa0/[^]

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

General question on DB normalization

Dewald3-Dec-09 20:59

Dewald

3-Dec-09 20:59

Hi all, I have what might well be considered a stupid question but fortunately none of you are my employer or former varsity prof so I'll ask away Smile | :)

I've always endeavored to keep my databases normalized to the best of my ability and, for the most part, I think I'm fairly well set in that line of thinking.

At my current company though some of our databases are anything but normalized. When I challenged my boss about it he responded that it was done deliberately because it is not supposed to be a relational database. It is a DB that accepts, stores and processes transactions at quite a fierce tempo and they didn't want every INSERT or SELECT statement to have to internally cross reference lots of other tables.

Fair enough, it makes sense. But then it occurred to me that I probably don't know enough about DB design because I would never have thought of that. I would simply have designed a normalized DB because that's the way I'm programmed.

So, I turned to Wikipedia but even there I can't seem to find an awful lot of information on non-normalized DB's but that's most likely because I don't really know what search terms to use.

So my question is:
1. When should a DB be normalized and when not?
2. Can anyone point me to an online article or two that deals with the topic?
3. Am I correct in assuming that a normalized DB is called a "Relational Database"?
4. What would you call a DB like ours here that is not normalized?

Mycroft Holmes3-Dec-09 22:17

3-Dec-09 22:17

Your boss may actually be right (I work with batch processes not transactions) because with every FK and index on a table the CRUD operation need to do the additional work to maintain the relationships. I still doubt it is a valid answer though, only a reporting database should not be normalised IMHO.

Can't help more than that and I hope you find the ammo to shoot this one down.

Never underestimate the power of human stupidity
RAH

Dewald3-Dec-09 22:44

Dewald

3-Dec-09 22:44

Thanks, but I'm not disputing that my boss is right. I think he has a valid point so I'm not really looking for ammo to shoot his argument down Smile | :)

I'd just like to make sure I really understand DB design and not blindly stick to normalized DBs if that's not always the best way to go.

Mycroft Holmes3-Dec-09 22:56

3-Dec-09 22:56

Ah but I think blindly sticking to normalised databases is the correct thing to do. The only exception I have found is if you are creating a summarised table for reporting, I often include the report descriptors in the summarised table. This has gone out of fashion as we are starting to use cubes to support our reporting and they are whole different matter.

Never underestimate the power of human stupidity
RAH

i.j.russell4-Dec-09 1:19

i.j.russell

4-Dec-09 1:19

The term that you are looking for is Denormalization.

In general, databases should always be normalized to at least 3NF unless there are specific areas where performance of reading/writing is a problem. However, most modern RDBMS like Sql Server are extremely efficient at joining tables using indexes so many of the reasons for denormalization are no longer applicable.

Eddy Vluggen4-Dec-09 3:01

Eddy Vluggen

4-Dec-09 3:01

Dewald wrote:
1. When should a DB be normalized and when not?

You normalize it by default, removing redundancy. Once the database becomes too slow (which is usually not due to the normalization, but lack of indexes and such) you start to optimize/de-normalize/add redundancy.

You'll waste a bit more space and gain some speed.

Dewald wrote:
2. Can anyone point me to an online article or two that deals with the topic?

The Oracle FAQ[^] also contains a link to the reverse process.

Dewald wrote:
3. Am I correct in assuming that a normalized DB is called a "Relational Database"?

The fact that there are relations between the data doesn't mean that these have been normalized.

Dewald wrote:
4. What would you call a DB like ours here that is not normalized?

That depends on what you're using it for. If it's a blob-store or a container for hashtables, then I'd say that it's an optimized datastore and I'd make sure to give credit to the optimizer.

If it's relational data that you're storing, then I'd call it an "accident waiting to happen" Smile | :)

I are Troll Suspicious | :suss:

DB Normalization - Further reading

Dewald4-Dec-09 3:07

Dewald

4-Dec-09 3:07

Great, thanks!

David Mujica4-Dec-09 3:37

David Mujica

4-Dec-09 3:37

Actually Wikipedia has a good explaination of Normalization.

http://en.wikipedia.org/wiki/Database_normalization[^]

Chris Date and Edgard Codd have written numerous books on the topic and are frequently used in Universities as the course textbook. I had to study them when I was in Graduate School.

[Message Deleted]

Uma J3-Dec-09 20:24

Uma J

3-Dec-09 20:24

[Message Deleted]

Re: Exporting data from stored procedure to excel spreadsheets

dan!sh 3-Dec-09 20:46

Export records to excel from sql

3-Dec-09 20:46

Check the link in my reply to the post below. Hope it helps.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

padmanabhan N3-Dec-09 19:17

padmanabhan N

3-Dec-09 19:17

Dear friends,

I need to export records from sql to excel. I know how to export the whole record to excel. But my requirement is, i have to insert 10 records in sheet1 and another 10 records in sheet2 and so on.... a help or some links would be highly appriciated..... (i dont want any packages like DTS)

Padmanabhan
My Articles:
Articles[^]
My latest Article:
Word Automation[^]

Re: Export records to excel from sql

T21023-Dec-09 19:29

T2102

3-Dec-09 19:29

The only direct way I know how to do that with separate sheets is with ADO and COM.

Re: Export records to excel from sql

dan!sh 3-Dec-09 19:39

How do I make a MS Access Subform, A Special Case...] [modified]

3-Dec-09 19:39

Check this[^]. I think this should help you.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

new_phoenix3-Dec-09 14:28

new_phoenix

3-Dec-09 14:28

I am working on a MS Access database that has a central table with a composite key consisting of seven elements, and another table also with the same seven composite key elements. The central table composite key is unique thereby making it a composite primary key. The other table has multiple records of the composite key. In other words, there is a one to many relationship between the main table and the other table using composite keys.

I need to create a form with the composite key elements on the top of the form and a subform with the multiple records matches to the composite key from the main form. The problem is the MS Access only provides the ability to create a subform using three elements from the seven elements of the composite key.

What would be the solution to allow me to create a subform that matches all seven elements of the composite key form the "many" table to all seven elements of the primary composite key from the central table?

Confused | :confused:

modified on Thursday, December 3, 2009 9:26 PM

Re: How do I make a MS Access Subform, A Special Case...]

Mycroft Holmes3-Dec-09 18:43

Options besides sql dependency?

3-Dec-09 18:43

A seven field composite primary key Dead | X|

- sounds like a disaster has found somewhere to happen. This is a BAD idea, I have no knowledge of the performance issues with such a mess but the sheer cumberomeness of forever doing a 7 key join would drive me to drink.

I recommend that you insert a primary key into the central table and populate it, then turn autonumber on.

Now add the foreign key field to the other table(s) and use the 7 key horror to populate the field.

Make your relationship using these fields. You must make sure you support the change within your CRUD code.

Never underestimate the power of human stupidity
RAH

prommel3-Dec-09 11:30

prommel

3-Dec-09 11:30

What are some options to replace sql dependency using sql server with multiple users? I like how sqlDependency works but without some changes to my database I can't determine what was added, modified , or deleted. So when I receive a notification I'm forced to reload the dataset and refresh. While this is not a major problem with a small dataset, as the dataset grows the time required to do this will be come unacceptable. I know one option is to continue using sqldependency and only load the data displayed for the current day on the calendar. So is there any other ways to keep data sync'd in a multiple user environment?

Thanks,
Paul

Some more information:

Application is a scheduling program
Use sqldependency for notification only
Use linq to sql for actual data retrieval

Re: Options besides sql dependency?

Mycroft Holmes3-Dec-09 14:13

3-Dec-09 14:13

I'll be interested in any responses to this one, I have never worked with SQLDependancy. I insist on minimum recordset size for the UI, any more than about 500 records gets looked at severely and usually the design is changed to meet the requirement.

Where I have to load a large volume I often allow the user to make a number of changes and then reload the dataset.

Never underestimate the power of human stupidity
RAH

database design

AndyInUK3-Dec-09 4:09

AndyInUK

3-Dec-09 4:09

Hi,

We have got hundreds of tables and SPs but no diagram. Basically we just keep on adding new tables as per requirement or delete table and there is no info.

What i want to implement now is - a whole database diagram - So what would be the best way keeping in mind that database is big if not huge.

Thank You
Andyyy

dan!sh 3-Dec-09 4:23

3-Dec-09 4:23

Which database?

With SQL, it comes as a built in feature. Is that what you are looking for?

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

AndyInUK3-Dec-09 5:21

AndyInUK

3-Dec-09 5:21

Yeh it's SQL - sorry i mean Diagram.
Should i just use visio and find my way out but that means going through hundreds of tables and sps and understanding them..Looks like i will never get time to sort this out unless there is quicker way which is also good.

dan!sh 3-Dec-09 6:01

3-Dec-09 6:01

If you expand the [DatabaseName] node, the first thing you will see is diagram. Although AFAIK, it will not help with stored procedures.

You can also check this[^] link. Might be of some help.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Mycroft Holmes3-Dec-09 14:09

3-Dec-09 14:09

You need to be careful with SQL diagram designer you could end up applying foreign key constraints while building a diagram. SSMS will not let you save the diagram if the data does not match the constraint, so save OFTEN, this is a good thing but you are going to be VERY frustrated chasing down the data errors.

I would use visio unless you actually want to clean up the data. Personally I would opt to clean up the database there are some enormous benefits to be gained from a well structured database.

Never underestimate the power of human stupidity
RAH

dan!sh 3-Dec-09 18:31