Best Practices for Database Design

Question

Best Practices for Database Design

Viewing 15 posts - 46 through 60 (of 145 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

davsec:

Ask those people who re-name their keys in different tables if they also like to change their own names as they walk from room to room in their house. That's essentially what they're doing.

--
Adam Machanic
whoisactive

davsec SSC Rookie Points: 36 More actions · Answer 2

Thanks Adam, I will remember that. LMAO!!!

Dan Avsec
MCSD, MSDBA, MCT

tjandrasa SSC Journeyman Points: 97 More actions · Answer 3

I would put description the same for two different tables. So I will have description fields in both Item and Customer Table. It is shorter.

For the primary/foreign key problem, usually I name my primary key according to table name, like customerID for Customer table. So if I find CustomerID on another table other than customer, I know it is a foreign key field.

DCPeterson SSCoach Points: 15266 More actions · Answer 4

I normally do not include the table name on any of the column names even if the column is just "Name" or "Description". Why? Because I don't like typing more than I need to and in a well written and formatted query it is very obvious which column belongs to which table. For clarity sake, you might want to use the AS clause to rename the columns in your result set to avoid any ambiguity though.

There is another reason for keeping them the same. If you have established data type standards along with naming standards it is very easy to find all the "Description" columns in the database to make sure they all have the same data type...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

davsec SSC Rookie Points: 36 More actions · Answer 5

I do alias my fields most of the time. But if you follow what I said regarding primary and foreign keys, you are implying "name" in one table is related to "name" in another table.

Dan Avsec
MCSD, MSDBA, MCT

tjandrasa SSC Journeyman Points: 97 More actions · Answer 6

If you always use word ID or CD after the field name, you always know that it is a primary/foreign keys.

DCPeterson SSCoach Points: 15266 More actions · Answer 7

In the relatively rare case that there is this kind of conflict I will normally rename the fK column. This does happen all the time if you use surrogate keys, and in that case you probably ought to use the table name in the primary key column...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

davsec SSC Rookie Points: 36 More actions · Answer 8

I guess I just prefer my data name specifically. I agree with keeping column names short, but they have to be clear and descriptive as well.

Dan Avsec
MCSD, MSDBA, MCT

Adam Machanic SSCoach Points: 15370 More actions · Answer 9

Only if all of your keys are IDs (IDENTITY columns, I'm assuming) -- what if your key is, e.g., a ZIP code, a social security number, an ISBN, or some other kind of non-IDENTITY, and in the case of SSNs and ISBNs, non-code? And to make matters even more complicated, what will you do if your key is composite?

(Note, I don't have very good answers to these questions -- just throwing them out there!)

--
Adam Machanic
whoisactive

davsec SSC Rookie Points: 36 More actions · Answer 10

"In the relatively rare case that there is this kind of conflict I will normally rename the fK column."

Do you find that to be an acceptable solution? The moment you start introducing exceptions into your stardards, your stardards pretty much become pointless.

Dan Avsec
MCSD, MSDBA, MCT

DCPeterson SSCoach Points: 15266 More actions · Answer 11

Yes, I find it usable.

On the flip-side, standards that are too rigid are often impossible to implement. If the process of data modeling could be reduced to a set of standards "checklists" I would be out of a job. An architect must have some leeway within the standards. This is where experience is required to make decisions based on guidelines, or priorities that are, to one degree or another, mututally exclusive.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

tjandrasa SSC Journeyman Points: 97 More actions · Answer 12

When designing tables and field I try to avoid using SSN or zip code as primary Key. What if the customer give the wrong ssn and they want to change it? It will be hard to change it although we can cascade it.

Well it's just me.

davsec SSC Rookie Points: 36 More actions · Answer 13

I totally agree with what you are saying about very rigid standards. When I develop a database, I try to use naming in a way that would be intuitive to the user.

Dan Avsec
MCSD, MSDBA, MCT

tjandrasa SSC Journeyman Points: 97 More actions · Answer 14

I agree, a lot of times I need to rename the fk column, like for manager id that actually employee id.

davsec SSC Rookie Points: 36 More actions · Answer 15

So you change your name as you move from room to room. LOL. Thanks Adam.

Dan Avsec
MCSD, MSDBA, MCT