I've been away from Access programming and almost all my other database stuff for a while, but the tide is coming in now and it's time for more work. My first project was an issue tracking database. It's a bit of a mess and needs some help but we use it all the time so I'm hesitant to revamp it too extremely. That said, a lot of new features are being requested. Some of the latest get very specific to the type of issue we are tracking.

They have decided that when the issue is to add a new product to our clinical system (Catalog - New), they have several pieces of info they would like tracked that are not applicable to other types of issues we track. Historically we have followed this kind of thing with "updates", a text field that references the original issue and is reported with the original issue. They want something custom tailored to type of info they need, for fairly good reason.

So I am struggling with the proper table layout to address this issue and subsequent ones. I *could* build a single table for Catalog-New items with the issueID as the foreign key and had the four or five attributes they are requesting. The other options I see and this is more work but probably more proactive is to have a table with IssueID, CategoryID, MilestoneID and MilestoneData. In this case, I would need a lookup table for the Milestone names but it would be more expandable as they make up some more of these. The downside, as I see it, is that it is basically capable of taking one piece of info per milestone. I may find myself in a place where that is not enough.

I was hoping you guys could help me think about his a bit, what is the more normalized version and what has the most potential for growth?

Moved to database concepts & design

Thanks for the move George, I placed it in access out of reflex.

No probs mate

See if this matches your situation: http://www.sqlteam.com/article/imple...-in-sql-server

Often called the "subtype/supertype" model.

Hi georgev,

Very interesting article in that link.
Now I'm trying to understand why is it that much better to use the Subtype/supertype model, where you have a table that holds the common attributes, and in separate tables the other attributes specific per type of entity (to use the examples in your link, the common table holds the persons attributes, and the other tables hold specific info about students/teachers/parents)

As I see it if you would have only the subtypes tables(students/teachers/parents) that contain the common attributes as well, it would help inserts and updates, you would do an insert only on one table not on two tables.

I suppose the main advatage of the subtype/supertype model is that it can enforce the uniqnesss of one person row within the other categories like students/teachers/parents, if this uniqness is wanted. Otherwise with only three different tables of students/teachers/parents you cannot know within the students tables if that person is already a teacher.

Can you please let me know if my understanding is correct, or if it's easier for you, what do you think is the main advatage of the subtype/supertype model.

Thanks,
Ronnyy

Further advantage - helps avoid update anomolies. A female teacher may parent a student and be a student themselves. Then she gets married and changes her last name. Supertype - one change. Three *broadly* duplicated tables - three updates.

The person is a single entity (super type) who performs one of several roles (sub types).

This is a sense rule I try to apply to all my designs - "if I do this, how might it eff up any updates?". Ultimately, at its rawest form, this is what underpins normalisation also.

pootle flump ur codings are working excelent.

George,
This seems like just the ticket, a sort of hybrid of the two things I mentioned. It allows me to add the extra fields to the subtypes I am aware of while waiting to add the relevant tables or fields where they are not yet decided. Thanks a lot.

I agree that in the case where the sane person may be in the same time a student a parent and a teacher, then the supertype/subtype model maintains the redundancies to a minimum and only with separate tables it will be a mess.
And I don't see any other way you could do it.

However if the requirement is that one person can be only a student/parent/teacher at a time then I see some disadvantages as well:
- you have to maintin from the application level the logic on which table to updated if some fields change. As you said, if she changes her name you have to update the base table, but if she changes for example the university then you have to update the students table.
- the same for inserts, from the application you should know that to insert a new student it's acctually two inserts in two different tables.
- I don't know how easy and fast it is to retrieve the data from the base table and the subtables, I assume you would have to do at least a join. For the denormalized tables(only subtype tables without a common base table) you do only one select.