Table column name design

Question

Let's say I have the following tables: Customer and Staff.

Customer (CusID, CusName, CusAddres, CusGender)
Staff (StaID, StaName, StaAddress, StaGender)

vs

Customer(ID, Name, Address, Gender)
Staff(ID, Name, Address, Gender)

Which design is preferred and why?

Answer 1

SQL employs a concept known as domain name integrity which means that the names of objects have a scope given by their container.

Column names have to be unique, but only within the context of the table that contains the columns. Table names have to be unique, but only within the context of the schema that contains the tables, etc.

When you query columns you need to reference the schema, table and colum that you are interested in, unless one or more of these can be inferred. Unless your query is so simple that it only references one table, you're going to need to reference the table name directly or by using an alias, e.g. Customer.ID or C.ID from Customer C, etc.

The first option is a throw-back to technical requirements for uniqueness of all column names, which applied to old ISAM databases and to languages like COBOL in the 1960s and 70s. This got dragged along for no good reason into dBase in the 1980s and has stuck as a convention well into the relational and object DBMS eras. Resist this outdated convention.

The second approach is much simpler and more readable. Simpler and more readable code is easier to write and much easier to maintain.