table design 1

[DBrack]

Hello all,

I have some questions about some of the tables that I am looking at (1) Is it ok to have LastName, FirstName, MiddleInitial in a single column separated by commas? (2) Is it good to have columns such as Problem 1, Problem 2 .. Problem 5 in the table even if the number of problems won't change after some time? What could be the disadvantages of having such columns? (3) 2 or 3 tables in the schema have similar columns like Manager, phone, extension etc ... Is it good to separate out manager details into a new table and reference them with ids?

These tables are currently in an access 2000 database that's going to be migrated to oracle 9i or 10g. The forms reports are referencing these tables. It seems the migration assistant can migrate them all to oracle wihout a big deal. Access queries will be converted to oracle views... Is it required to fix these tables (and reports, forms) in access before migrating to oracle?

Thanks in advance.

 

[SantaMufasa]

D,

Is it ok to have LastName, FirstName, MiddleInitial in a single column separated by commas?

Not advisable since it requires additional processing to parse out individual components each time you need to reference any one of those columns individually.

Good DB Design Form: break out, into individual attritubes, distinct data items to their elementary, individually referenceable components.

Is it good to have columns such as Problem 1, Problem 2 .. Problem 5 in the table even if the number of problems won't change after some time? What could be the disadvantages of having such columns?

E.F. Codd's First Normal Form rule discourages Repeating Attributes (such as Something_1, Something_2,...Something_n in a table). Repeating attributes usually indicate the existence of a distinct child table that should like to the original parent table via foreign-key-to-primary-key data relationship.

Good DB Design Form: Avoid repeating-group definitions within an Entity (Table).

2 or 3 tables in the schema have similar columns like Manager, phone, extension etc ... Is it good to separate out manager details into a new table and reference them with ids?

E.F. Codd's Second Normal Form rule says that data in a table should natively depend upon the Primary Key of a single table. When you analyse the attributes "phone, extension, etc" (as they apply to "Managers"), those are attributes of "Persons" (or more specifically, "Employees")...in fact, "Managers" are themselves "Persons"/"Employees". And in additional fact, "Managers" are "Managers" as a result of their relationship to other "Persons"/"Employees". Therefore...

Good DB Design Form: Use Foreign-Key-to-Primary-Key relationships within the same table to represent hierarchical relationships.

Implemention of the above suggestion occurs by have a "MANAGER_ID" column in the "Person"/"Employee" table that is a foreign key that points to the primary key of some other row in the same table. Resist the temptation to build a "MANAGER" table since typically all "MANAGER" rows are redundant to rows in a "PERSON"/"EMPLOYEE" table, along with their attributes, "phone, extension, etc".

Is it required to fix these tables (and reports, forms) in access before migrating to oracle?

So long as the Access-based (sub-standard in this case) design does not "cast in bronze" the new design under which Oracle will live, then "No, Is it NOT required[/I][/B] to fix these tables (and reports, forms) in access before migrating to oracle."

There is, however, a principle under which I labor and have found great success:

Good DB Design Form: Let "Bad Data" live as brief a life as possible.

In your case, the above priciple implies that you should correct the flaws in your present database design prior to a migration to Oracle. But, if rectifying those flaws introduces risks to your current (Access-based) production system, I would probably opt to resolve the flaws during the migration/new-development phase of your conversion to Oracle.

Let us know if you find these thoughts useful.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.