Request for Access Database Structure Suggestions

[Nephalim]

This is a request for help in creating an ERD structure for an Access database.

I've found myself in a commitment to develop a scheduling database. By scheduling, I mean this: There are Stores and there are Merchandisers who must stock the shelves at the Stores on scheduled days during the week. I need to be able to efficiently store data about the stores and merchandisers and be able to print reports of the schedules on a week by week basis (mon-sun), showing which merchandiser works on which days, AND at what time of day (morning,afternoon,evening) the work. Additionally, i need to print daily schedules for each merchandiser, showing all the stores they are to visit. I have constructed two models; which of these is better, or is there a better model?

Store with foreign key (FK) in Week
Week with FK in Weekday (mon-sun)
Weekday with FK in Timeofday (morning,afternoon,evening, +more)
Merchandiser with FK in Timeofday

Week with FK in Weekly Schedule
Store with FK in Weekly Schedule
Merchandiser with FK in Weekly Schedule
Weekly Schedule includes fields Mon-Sun, each denoting an enumeration of timeofday

Please, any suggestions?

[Grizzly]

ummmmmm...I don't get it

Do you have a first draft of an ERD diagram or a schema of some sort? I don't really follow your paragraph format relationship definitions there.

[Nephalim]

Making me work! Here are some pics of the two:




account is "store"

[Nephalim]

I've also considered removing the Week entity from pic2 all together and just going with Day... did i mention i hate Access? Arg.

[Grizzly]

Hey excellent, I think I follow your drift a little better now. I'm at work unfortuantely, so I can't really put any thought into it until this evening, but I just wanted to touch base so you know we didn't forget about you

I've been doing a bit of reading on data modeling lately, so hopefully I should have some constructive input ready for you later this evening.

[Nephalim]

That would be AWESOME! Thank you.

[Tekime]

At first glance I would feel inclined to say that three tables would suffice:

Merchandisers
Stores
Appointments

Appointment can hold the date, time, merchandiser_id and store_id. Then between VB and any macros you can do the calculations. Just seems like a lot of info to be storing if it isn't necessary. Then again, today was perhaps the longest day of work I've had this year

Maybe I'll take a nap and look again

[Nephalim]

We've been looking at all these options, and the best option we've discussed yet is the one in pic1. The factor that has deterred us from pic2 and the option Tekime mentioned (although similar to pic1) is that the record count is going to be astronomical. That is, NUM_STORES * 7_DAYS_OF_THE_WEEK * 365_DAYS_OF_THE_YEAR = A_BIG_FING_NUMBER of records, and this project is supposed to last N (number greater than 5) years!!!

[Nephalim]

What I'm confused about is how have DBAs managed scheduling databases in the past? There must be an efficient AND logical way of doing this. I know both of you, Grizzly and Tekime, are experienced programmers and I thank you both for your help! If someone did have a better idea before tomorrow, I would LOVE to hear it so that I could implement it quickly and get this headache done with. Otherwise my choice is going to have to be the one in pic1, even though having mon-sun fields seems counter-intuitive to Data Modeling Theory (I could be wrong )

[Grizzly]

I really wouldn't worry about the number of records. I've worked on some fairly beefy databases before. Most enterprise level db's have millions of records, and in some cases thousands, or even tens of thousands of tables.

Usually, the more scalable and extensible you make a data model, the more complex it becomes. By "complex", I mean entities and their attributes are broken up as much as possible, and related in as many ways as possible. Often times these relationships between entities, and other entities, or entities, and their attributes can generate thousands and thousands of records within minutes. But hey, databases are built for that now aren't they?

If you're actually going to use Access - than you might have something to worry about. But if you're going to use a real DBMS (as in, non-file based), it's really not an issue.

Anyways, I took a stab at what I *think* you're going for, and here it is:



I think you're right, in that you don't need to store data for weeks, days, or even times for that matter. Simply storing the scheduled date of delivery/stock should be enough. A full date field from a database should be enough to tell you the day, the week, the time, the number of seconds since epoch, etc etc. However, I did start to think about how different stores have different "slots" in their delivery schedules. By that I mean, Store [A] might have delivery times of "Morning, and afternoon", while Store [B] might have delivery times of "Morning, Lunch, Afternoon, Evening, Late, Graveyard, etc" I'm really not sure if this is true or not, but I'm assuming that these stores don't ALL share the same time(s) they expect delivery. Some may only take morning delivery, some only night, etc etc.

So that being my assumption, I came to the realization that there should be a many-to-many relationship between "stores" and "times." So, as you can see in the model, there's a table that bridges that many-to-many relationship between those two entities. (Store_Time_Relationship_Table)

I think the rest of it is fairly straight forward really. You have a merchandiser entity, and they need to schedule deliveries for stores, at some predefined times of day. Simply establish a relationship between a "merchandiser" entity, and a "time" entity, and associate an exact date with that relationship, and there you have it. You automatically know what store the delivery is going to since each "store" entity has it's own unique "time" entities.

Other things you'll notice is that there are the combined primary keys that I use in the relationship tables. These are a fairly common thing, but I'm not sure how experienced you are with them so I figured I'd make mention of that fact. Combined keys are a fairly efficient, and clean way to define many to many relationships between entities.

Anyways, I hope that made some sense. I really doubt that it's the right solution as-is, but my hope is that it's a step in the right direction for you. Designing a sound data model is nothing to be taken lightly. It usually takes a few itterations (I've been through more than 30+ versions before reaching a 1.0 in the past), a lot of back & forth with the client, a lot of requirements analysis, etc.

Without having more exposure to the project at hand, this is the best advice I can give you. I would imagine there are some important requirements that the client has communicated to you, which you haven't told us. And I would also imagine that there are fairly important requirements that the client knows about, but hasn't quite articulated it in such words to you.

The entire point of good data modeling is to architect in such a way that your data model can turn on a time when it has to. So when the client comes back 9 months down the road and says, "Hey, I want to add this, that, and the other - oh and I want this to work differently too", your data model will hopefully make that whole process a little more palatable for everyone.

[Grizzly]

Alright - it's been a long day, lol. I take back what I said about making a many-to-many relationship between "stores" and "times". What I should've done is removed that "Store_Time_Relationship_Table" and simply placed the Store_ID in the Time_Table.

That would support the statement I made which said: "You automatically know what store the delivery is going to since each 'store' entity has it's own unique 'time' entities."

Each store having it's own unique time entities denotes a one-to-many relationship, not a many-to-many. Sorry for being so confusing here, I'm on my 13th hour of staring at a text-editor today, and I've had enough.

Luckily, a female just called so I'm goin' out for a few drinks, heheheh. Good luck with it Nephalim. I'll check this thread periodically at the office tomorrow to see if you have any follow up questions, or hopefully, to see your victory dance

[Nephalim]

I'm testing data for this setup:


Grizzly, I can't imagine how you've become so experienced except through hard work --- hard work like the thought you've put forth on my own projects now and in the past without pay or reward --- since you're a mere two days older than I Nonetheless, as always, I'm gracious. Good luck (if you needed it) with the lady. I'll post the results of my test as soon as i can.

[Grizzly]

Glad I could help. The model your going with looks pretty sound to me. It's exactly what I was shooting for when I followed up with the "ix-nay on the ne-to-many-oay" reply.

Personally, data modeling is something I take a great interest in. That's why I've been at least *trying* to do some actual reading on the subject lately. Most of what I do know was learned from trail and error, past experience etc. Like with anything, that approach can only take you so far.

As far as luck with the ladies goes, muchos gracious Somehow a group of local drinking buddies of mine convinced me it would be a better idea to go hang out with them tonight, instead of going to Providence's biggest dance club with 2 swedish exchange students. Don't ask why - but I ended up ditching the females to hang out with the guys. See - even I'm a complete and total moron at times, lol.

And you're a 9/18/79 baby? Pfft.....you damn fool kids will never learn When you were rolling around in your diapers I was....well, doing pretty much the same.

Anyways, good luck with the project Neph. It looks like you're off to a good start.

[Tekime]

Man, Grizzly, you are a wiz with database design.. I'm also doing a bit of reading on data modeling (or rather, 'Database Systems' under Addison-Wesley), and Access 2000 as well (Access is primarily for a future project at work).

As far as I can tell, what you have right now looks like a pretty solid model Packet... er, Nephalim. Maybe I'll just call you Packenephalim so I can address you universally :P.

I wondered about two things though. You're right saying record size could get high; exactly how many new appointments will you be adding every day? As Grizzly mentioned Access isn't really the best for a huge number of records. Our CS department records a record of every call they take to an Access database, and I'm guessing there are about 1,000+ calls every day to CS dept. It's starting to get close to 100k records and it is unreasonably slow, confuses rep ID numbers between transactions, and crashes periodically on a few workstations.

If you have a huge number of appointments it would get big fast. I'm not sure if you meant the merchandisers literally followed a schedule, but if they all do it might be feasible to have a schedule table which reduced the number of records by recording a span of time.

A little different than the original schedule table posted, though. Store start and end dates for the schedule, and have a many-to-many realtionship between schedules and appointments. Each appointment entry can have a day of week field. This way, instead of creating 365 entries for one merchandiser for the next year, you can create one schedule based on weekly visits and have it span one year. The number of values per merchandiser would rarely be larger than 7 * the number of accounts they supply (unless they visited one supplier multiple times per day).

This is off the hip, but perhaps something like this:

Stock_Schedules <--

I don't know how many normal forms that violates, but I am exhausted (it's 3:30am here on the east coast ). Either way, it's another perspective on it if you plan on having a huge number of entries. I'm sure Grizzly can help to solidify (or entirely debase ) the model.

P.S. Don't tell me you're both 70's babies, too? 8/21/79 here, guess I'm the old man (Wait a minute, that's actually kind of depressing... darn you Grizzly and your uber mind!) :P I'm going to bed.

[Grizzly]

Nice suggestion Tekime. That is actually an approach I toyed around with in my mind when I originally read this thread. It would make sense to me, that any given merchancdiser won't change their schedule from week to week too often. That being the case, one might be able to reduce the amount of data entry by laying out a week or so, and saying "this applies to the next 6 months."

I would have to think that would be the best thing. But that would also be a fairly large assumption, which could/should be reconciled with the client.

I think you have a great design there actually. The only thing I think could be done better, is the tbl_Shifts relationship. In that model, you can either: A) Allow "Stores" to share a general pool of "shifts" on which they can accept delivery, or B) Force "Stores" to re-enter their "shifts" for every "Schedule" entered.

Of course, this data re-entry could be by-passed with some slightly convoluted code - but it would be a hack at best. I don't have a good suggestion right now, other than saying I think the tbl_Shifts situation could be thought through a little better. It seems to me that a more direct relationship between shifts and stores is something that could greatly improve the flexibility of this model.

And wow - how is it we we're all born at around the same time? Was there something in the planetary alignment during that time that made us all endlessly interested in technical architecture? Maybe we'll never know...