Opinions on database design... 1

[lorirobn]

Would love to get feedback/advice on a database design modification I am working on.

I am working on a database of all rooms in a facility.
I have created a RoomHeader table that contains all the rooms in the building, and another table that only has Guest Lodging. There is a one-to-one relationship between Header and Guest Lodging.

Now I have found out that rooms in Guest Lodging can change temporarily, to be used for purposes other than guest lodging. It is this situation that I am trying to modify my database for. Currently, only rooms that are guest lodging exist on guest lodging table, and all forms, reports, etc. work on this premise.

One workaround I thought of was to add an 'inactive' flag to guest lodging table, marking a room as inactive when it becomes not guest lodging. This would probably involve changes to most forms and reports, though, to exclude the inactive records, and possibly even adding this indicator to primary key. However, another design change I thought of was to move the non-lodging record to another table, like a 'holding table', deleting it from the lodging table. When its use changes back, delete the record from the holding table, write it back to the lodging table. The record would be 'toggled' back and forth between two tables. Does something like this make sense? Does anyone know of documented sample databases using a concept like this, that I could check out?

Would appreciate any feedback on this, especially on the toggling table option. Thanks.

lorirobn

 

[LittleSmudge]

One to One relationships always ring load alarm bells.

You seem to be making this way too complicated.

I developed an estate management database some time ago that seems similar. It was able to cope with :-
Farms containing buildings, fields etc.
Each building contained many rooms
Some buildings were rented to tenents on a 'funrished' basis so
Each room could contain furniture

All of the above took ONE self refering table.

So for your app I think you should be looking at something similar.

The property is an object
That object can contain many other objects.
Each object can have many properties ( properties in the database sense not in the real estate sense )

Each property is a field in the table.

One property will be "Letable" Type Yes/No

Based on the value of the Letable field you can iether open different forms showing just the applicable fields OR have one form ans show/hide some controls dependent on the setting of Letable. Put code in form's OnCurrent event to set the Visible property of those controls.

No need for storage tables
No need for hiving off data on a temporary basis
No need to reinstating that data later



'ope-that-'elps


Think of

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lorirobn]

This helps a lot.

I originally broke the tables into two to keep things neat and clean and organized. There are about 90 fields that are relative to Guest Lodging only, and I thought it would be better to break these on their own. And there will be other room types later on that will add to the field list, some with a completely different list of fields, like Public Bathrooms. So, I could add this to the 'one table' as well? I guess I didn't think it'd be ok to have a huge field list like this. But it sounds like you had one table with many many fields, each pertaining to a different property. It would certainly make things easier, as far as keeping the tables in sync.

In table design I've done in the past (other db's), you could have a 'Group Level' Field, and put related fields within that group level (ie: Guest Lodging Group). This keeps the table organized for readability. I don't believe Access has a way to do this, does it?

I am going to do some web surfing for examples.
Thanks so much Little Smudge... I guess the overall theme for me is to not complicate unnecessarily...
Lori

 

[LittleSmudge]

You can't 'group' fields together in tables in Access - but if you want to distinguish a certain collection of field then you can always add a common prefix to their names.


With THAT many custom fields there may be a case for carefully thinking through a special table.
Main table has a PrimeKey that can be an AutoIncrement or a NaturalKey. The secondary table then has a Foreign key that is populated by code when the additional data is required. Link the two tables with Referential Integrity enables and make the second table foreign Key Indexed and not allow duplicates. In that way you guarantee one-to-one.

However, before you extend this to another table for some other room type, first prove to youself that it is not just the names of the fields that you are changing. The second table can contain all sorts of additional data about all sorts of room types. ( You just have a roomType field that indicates what RoomType any particular record refers to. )
Make the field names generic in their description of what they contain and you can use the same tables for all of your 'additional room types'.

Eg In the Estate Agency database I developed the main work was done in one table. In the case of a furnished letting a record in that table could represent :-
The house
A Room
A piece of furniture
A item of inventory
etc.. ..




'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[johnherman]

I would take a slightly different approach. First, you have a room. This is a supertype (as defined in modeling world). Below the supertype are subtypes. Each type of room is a subtype and will have certain characteristics about it which make it different from the other room types. Finally, you have a room assignment. This assigns the room to a particular room type for a period of effective dates.

So, I go for two major tables, Room and RoomAssignment along with a subtype to Room (Guest Room, Conference Room, Professional Suite, etc).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[BobRodes]

<I originally broke the tables into two to keep things neat and clean and organized.

This has to do with our discussion in your other thread, Lori. You're not separating in your mind the database design from the database presentation. It's easy in Access to confuse the two, since there are lots of ways to directly present raw tables, especially if the designer is also the person who uses the data from day to day.

If you want to keep things neat and clean and organized, first design your database in a way that optimizes the robustness of the structure. Then build a form that shows that data the way you want to see it. If you want to see only guest lodging, add a field to your rooms table that defines the type of lodging, and then build a query that only shows rooms of the guest type.

HTH

Bob

 

[BobRodes]

lespaul likes to recommend this article, and I find it an excellent one.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

 

[lorirobn]

So do you also agree with the concept of one table, Bob?

I have worked on many databases before, including other relational DB's, but not Access. One-to-one tables were a common practice, to keep one table from getting too out of hand size-wise and also to keep similar data together. But it seems like in Access, this is looked down upon.

I am in the process of redesign, and have come up with one main table...

 

[BobRodes]

Well, one to one tables were common practice in dBase II, when there was a limit of 65,536 records. I have worked with numerous ISAM databases (RBase, Clipper, Paradox, Access) and only used one-to-one tables very rarely.

However, there are sometimes architectural reasons for one-to-one relationships (as opposed to overcoming limitations in the RDBMS). I have a somewhat macabre example: you have a list of people, say 5% of them are deceased. If you have a "date deceased" field in your main table, it will be 95% empty, thus using space. If you put the date in another table and relate it, it will usually be more efficient in terms of space, although joining the tables will require time overhead.

I would not break a table into two without a compelling reason. If I understand your data correctly, I would keep this in a single table. If you are in fact keeping track of assignments as well, I would do as johnherman suggests.

The fundamental structure of a well-formed database is the one-to-many relationship. If you can put all of your data into this type of relationship, you'll have a well-formed database. All the rest is embellishment.

If you create a query, and base your forms and reports on the query, you can change the underlying table structure all you want, and all you have to change to make the forms and reports work is the query. So, your architecture isn't very flexible. What if tomorrow you need a third table for some reason, and have to go change your forms and reports again? Use the query as a "broker" between the data and the presentation of the data. If the query keeps returning the same fields, you don't have to change the forms or reports, yes?

Bob

 

[lorirobn]

Thanks Bob, for the post, and also for the invaluable article you suggested. It was incredibly helpful, and I will use the steps documented in my table redesign.

To LittleSmudge and JohnHerman - thank you as well. I take all your helpful advice in as I work on this database.

Lori

 

[BobRodes]

You're most welcome Lori, good luck.

Bob

 

[johnbrookes]

Dear BobRodes,
If this post is misplaced, I couldn't figure out how to message you directly.
I am looking for a general explanation of ISAM. How does it work? How is it set up?
I don't think these are very difficult questions...
Thanks in advance
speechexpert(at)sbcglobal(dot)net

 

[johnherman]

I have responded to your other post. Please do not double or triple post your questions as the "Knights" of the Round Table will report you to management and you will get your butt slapped.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright