Well, I am creating my first "for real" database using SQL Server and I have a few questions. Some of my coworkers said that they don't make the key relationships linking the tables, but rather specify what needs to be done when coding (hopefully that makes sense). I am used to designing tables with the relationships/integrity. Should I leave these relationships out and have just a bunch of tables instead of a database!?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Getting Started with SQL 2
- Thread starter jshurst
- Start date
-
1
- #2
WhiteKnight57t
Programmer
Always But always Make the relationships in the DB
1) it helps to maintain itegrity -- (spelling not a strong point)
2) 6 months down the road you still know what joins to what and how
==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing
Tim
1) it helps to maintain itegrity -- (spelling not a strong point)
2) 6 months down the road you still know what joins to what and how
==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing
Tim
- Thread starter
- #3
That's what I thought. I really couldn't believe it when they said that. I might be right out of college, but that is the basis for what we learned!
Another question: When I set up my primary key as an Integer I can only use 4 for character length. Does that mean that I can only have 9999 records in the DB? And am I supposed to set the identity to "Yes(not for replication)" if I want it to automatically assign numbers?
Another question: When I set up my primary key as an Integer I can only use 4 for character length. Does that mean that I can only have 9999 records in the DB? And am I supposed to set the identity to "Yes(not for replication)" if I want it to automatically assign numbers?
-
1
- #4
Key relationships are important if you want to maintain data integrity. Wihtout a key relationship, you can insert a record into a child table without a matching record in the parent table or delete a parent record without deleting the child records. Allowing this is a very bad practice.
You also cannot set up cascading delete and update unless you have a key relationship.
There are occasions when the relationship may not need to be explicitly desfined and maintained, but they are usually involving data that is imported from elsewhere or when you may want to save data historically in child records but update the parent with current information (the classic products/orders tables come to mind. In this case you better denormalize, the parent table is now acting as a lookup table, not truly a related table.) But the exceptions should be few and far bewteen, not a standard practice.
I'd say in general your coworkers are wrong. Further programming what needs to be done in code rather than setting up relationships is setting yourself up for disaster when someone forgets in one process or actually changes data directly in the tables. Very poor practice.
Questions about posting. See faq183-874
You also cannot set up cascading delete and update unless you have a key relationship.
There are occasions when the relationship may not need to be explicitly desfined and maintained, but they are usually involving data that is imported from elsewhere or when you may want to save data historically in child records but update the parent with current information (the classic products/orders tables come to mind. In this case you better denormalize, the parent table is now acting as a lookup table, not truly a related table.) But the exceptions should be few and far bewteen, not a standard practice.
I'd say in general your coworkers are wrong. Further programming what needs to be done in code rather than setting up relationships is setting yourself up for disaster when someone forgets in one process or actually changes data directly in the tables. Very poor practice.
Questions about posting. See faq183-874
Integer takes up 4 bytes but the values can go higher than that, see BOL (Book Online, SQL Server help) for the definition of each datatype and exactly what values they can contain. You can use big int if int won't have enough values. Set setting the identity to yes will autoassign the numbers.
Questions about posting. See faq183-874
Questions about posting. See faq183-874
- Status
Similar threads
- Locked
- Question
- Locked
- Question
