Is this bad design?

[MorrganMail]

Recently got to take over the responisibility for an application which
is making heavy use of an Access database. It has been a long time
since I came in contact with anything database related and I must admit
I have forgotten a lot of the theory and was hoping you could help me
out. I have found a couple of things in this database which to me seems
like bad design but there might be good reasons for doing things this
way, what do you think?

Bad design or ok?

Tables without primary key.

Tables with a generated primary key i.e. an id where a number of
attributes in the table could be used as to make up a composite primary
key.

Tables with no relation defined which are obviously related, i.e. a
couple of the tables have the same attributes but are not related. In
some cases a table contains the primary key of another table without
the tables beeing related.

Any input would be greatly appriciated.

[Martin]

<Mo*********@gm ail.comwrote in message
news:11******** *************@d 34g2000cwd.goog legroups.com...

Recently got to take over the responisibility for an application which
is making heavy use of an Access database. It has been a long time
since I came in contact with anything database related and I must admit
I have forgotten a lot of the theory and was hoping you could help me
out. I have found a couple of things in this database which to me seems
like bad design but there might be good reasons for doing things this
way, what do you think?

Bad design or ok?

Tables without primary key.

IMO, If it is used to hold more than 1 record or is joined in a query that
is bad design. I have some tables which just hold titles or save selections
in which i dont define a PK.

>
Tables with a generated primary key i.e. an id where a number of
attributes in the table could be used as to make up a composite primary
key.

Bad design If I understood you. Like invoice# + customer in 1 field is used
as a PK that is terrible.

>
Tables with no relation defined which are obviously related, i.e. a
couple of the tables have the same attributes but are not related. In
some cases a table contains the primary key of another table without
the tables beeing related.

Many programmers are insistent on referential integrity so this would be bad
design. IMO this alone shouldn't codemn your previous programmer but in
conjuction with the other 2 things you said its bad design.

>
Any input would be greatly appriciated.

There will be no easy fixes. My suggestion is leave the bad db in place
while you create a new DB with proper PK's and relationships. Write a
import procedure for the old data. Update all his forms, queries, reports...
to the new DB structure. Make sure it works. Then import the data again. If
it works, then replace the back and front end together at the same time.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Bob Quintal]

Mo*********@gma il.com wrote in
news:11******** *************@d 34g2000cwd.goog legroups.com:

Recently got to take over the responisibility for an
application which is making heavy use of an Access database.
It has been a long time since I came in contact with anything
database related and I must admit I have forgotten a lot of
the theory and was hoping you could help me out. I have found
a couple of things in this database which to me seems like bad
design but there might be good reasons for doing things this
way, what do you think?

Bad design or ok?

Tables without primary key.

Tables with a generated primary key i.e. an id where a number
of attributes in the table could be used as to make up a
composite primary key.

Tables with no relation defined which are obviously related,
i.e. a couple of the tables have the same attributes but are
not related. In some cases a table contains the primary key of
another table without the tables beeing related.

Any input would be greatly appriciated.

There is plenty of debate about using autonumber primary keys.
Google for the pros and cons.

Relations can be created on the fly in the query builder and/or
SQL. Sometimes it's lazyness, sometimes it helps keep the
clutter down in the relationships window, and sometimes you
don't want to fight with a predefined relationship every time
you open a query. (e.g if you need inner, left and right joins
between the same two entities). You don't get Referential
Integrity that way, but if the RI is enforced by other
predefined relationships it's not a concern.

I personally only pre-define relationships where I want RI, and
just add them aas needed in the query when I don't need the RI.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[MorrganMail]

Thanks for your replys. You have been most helpful.