January 31, 2008 at 7:28 am
Hello,
I am trying to figure out the effective way to create realtionships between records in multiple tables but I am not sure how to accomplish this 😉
January 31, 2008 at 8:16 am
One table has a primary key, column or columns that identifies it uniquely. You can define this in SQL Server as a primary key constraint. A table that this table relates with will also have the same columns. This makes a relationship and is usually called a foreign key. You can enforce this in SQL Server by creating a foreign key constraint.
This is a pretty fundamental question. Unless I've misunderstood, I'd recommend you start with some basic books. It's really old, but it covers everything very well: Handbook of Relational Database Design by Candace Fleming & Barbara von Halle. There are lots of others. Once you've got the basics down for the theory, you'll need to learn how to implement them in SQL Server. McGraw Hill has a Beginner's Guide that might help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 31, 2008 at 8:25 am
No you understood correctly...I am very new to database design. I do appreciate the suggesstion of books.
Let me describe this to you and give me your thoughts. I have a large amount of data. All in different columns within a workbook. I have added all the data in respective tables but now I need to create relationships to columns in different tables. What are your thoughts 🙂
January 31, 2008 at 8:30 am
OK. Well, first, you need to identify logical seperations between the data. Each logical seperation can become a table. Once you establish a table, you have to identify a primary key. Once there you can begin establishing the relationships between the tables. It's actually difficult to describe this process in a small space and still communicate enough information to get you started. I strongly suggest hitting the books first. Otherwise, you're likely to dig a hole and then you'll have a long time digging out. Poor data design choices can result in terrible performance, bad data, bad data integrity... You get the picture.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 31, 2008 at 8:42 am
So far so good...I have decided a logical seperation of the data. I am currently putting the data in their own tables. So now I need to identify a primary key...thanks a lot...oh yes I am currently having Borders order that book for me. I will keep you updated on my progress.
January 31, 2008 at 3:16 pm
Is there anyone in your office that can help you? Is there a DBA in your office? The book just describes the basic steps how to design a database. In reality there is a lot more to think about when you design a database. First do you have the requirement from the users? You need to put in the business logic when you design the database. What is the purpose of the database? Is it going to be used 24X7 ?
Do you need to consider performance when you insert, update and delete data from the database?
I am not trying to scare you but just to want to realize it is not an easy thing to do!
February 6, 2008 at 7:54 am
Yes I do have requirements no it will not be used 24X. This database will be used to manage a large amount of data in a logical manner.
February 6, 2008 at 7:56 am
Can someone recommend a good url that will explain the do and don'ts of realtionships
February 6, 2008 at 8:32 am
This is a decent overview.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2008 at 9:15 am
As I continue on with desgin...I have decided that I wish to have multiple primary keys in a table...is there anything wrong with this design..what should I be prepared for as for as data integrity and maintenance?
February 6, 2008 at 9:21 am
You mean "compound" primary keys right? Because you can only have one primary key per table.
Although, you can have multiple unique constraints and refer to those as "alternate" keys because they can be used for foreign key constraints, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2008 at 9:24 am
Oops. Hit the "Post Reply" button too soon.
And no, this isn't a problem, depending on how you apply it. Multiple varchar fields of length 3000 would be a really poor design choice. We've built a system that used artificial keys (identity fields to be precise) in a compound manner in child tables. We had tables with up to 5 & 6 integer columns defining the primary key. Not only was this not a problem (well, it was a pain to type out the JOIN statements in the queries), but the designed eliminated bookmark lookups which lead to massive performance gains over the previous design.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2008 at 9:37 am
Technically a table can only have one primary key. You can have more than one unique index, and these are called alternate or candadate keys.
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply