Schema Question
-
Would someone be willing to take a look at this schema and make some suggestions? I am just not feeling like it is the right way to go, and am sure exactly what I need to do to tweak it...Thanks
http://www.imagehosting.us/imagehosting/showimg.jpg/?id=554699
Basically, the problem for me is this... There is a master EventTable, as shown, but each separate company can define there own lead times for such an event. Basically, Shipping could exist once in the master table, but each CompanyTemplate could have its own separate Shipping record, with different lead times for each. And the same could happen for CompanySeasonTemplate
CompanyTemplate Example
Company 1 could have Shipping and a lead time of 45, and Company 2 could also have Shipping, but a lead time of 35.
CompanySeasonTemplate
But, Company 1 could also have Shipping that occurs in Fall with a lead time of 30.
Furthermore, not shown here will be a projectEvent Table...but that is for another time
Does this make sense? I hope so, I want to get through this model so I can submit it to the client. Again, any help is much appreciated. Thanks
HC
-
So basically the CompanySeasonTemplate overrides the CompanyEventTemplate? In other words, normally Company A wants 45 days lead time for event 1, but in summer they want 30.
Another way of handling this issue would be to just use Season in the CompanyEventTemplate table and each time an event is defined for a company there would be four records inserted (assuming four seasons), each with their own leadTime. This has a drawback of additional administrative overhead for the users, but it makes more sense from a logical standpoint. If season is a factor in determining lead time, then why leave it out in some cases and include it in others?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Well, season is not ALWAYS a factor. Sometime, when setting up a Project (see Project Table), a user could input Company, Season and ProductCategory. But ONLY Company is required. So, if they choose Company, then the CompanyTemplate gets used. If they input a Company and a Season, the CompanySeasonTemplate gets used. And so on...make sense?
-
I think having an EventTemplateType(Company, Season, ProductCategory) table is best. Then have ONE CompanyEventTemplate table, and have FK to Company, Event, and EventTemplateType.... Looks better and more functional...Any other thoughts are still apprecaited. Thank you
HC
-
I'm always a bit hesitant to get too much into these kinds of discussions. I don't know enough about the business requirements to make any detailed recommendations, but from what you said about the Project table, I'm always very wary of NULLable columns. i don't like them and in many cases when you encounter them, it is due to a logical/conceptual problem with the design.
Also, I would question the need to reference Season in three seperate places. Would it make sense to define the events by season? That way your templates, which reference Event, are also implicitly defined over seasons...?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
So, what about the Schema I mentioned above,
http://www.imagehosting.us/imagehosting/showimg.jpg/?id=554884
-
I think your second schema (id=554884) is cleaner than the first. you've removed the redundancy of storing SeasonID in multiple tables.
I, too, am unclear on the business requirements so I'm hesitant to make strong suggestions. How about questions?
Are the xxTemplate tables really templates or will you be storing a record for each event? If you are storing records for each event, you could use one table like the CompanySeasonTemplate and default the LeadTime. Let the user change the LeadTime when creating the record. Maybe use a trigger to add logic to set default based on CompanyID and/or SeasonID.
By 'season' do you mean Winter, Spring, Summer and Fall? or Winter 2005, Spring 2006, etc.? If the former, you will lose data integrity when you replace values in future years. If the latter, you have a new record for every season which makes the default/trigger idea more likely.
One last question, is the season a determinant; does it decide the LeadTime or is it just another piece of information related to the event? The original post implies that the season determines the LeadTime. This doesn't sound quite right but again, I don't know your requirements.
I hope this helps more than confuses the issue.
-
I appreciate everyone helping...here is the functionality, in a nutshell...
An admin user can set up three types of Event Templates which are just events with lead times (in days)
One template for his WHOLE company...only ONE template can be in the DB per company.
Now, a user can have four company season templates...meaning that Company A can have one template for each season (Fall, Winter, Spring, Summer) Season DOES NOT determine leadtime. LeadTime is just another data point
And finally, Company A could have a template for Company A, Fall Wrapping paper. And they could also have Company A, Fall, Gift Cards, or Company A, Spring, Wrapping paper. They could not however have Company A, Spring, Wrapping paper TWICE. Only once. I would really like to enforce integrity on this...but not if its forced doesn't fit the design. Any thoughts? Thanks
Harry C
-
Ok, Last tiem I think..how about this
http://www.imagehosting.us/imagehosting/showimg.jpg/?id=557275
This schema allows me to keep constraints, but still have one table for templates. AK in CompanySeasonProductTemplate are CompanyID, SeasonID, ProductID, EventID with only CompanyID being NOT NULL... How about it?
-
Harry,
This looks a lot cleaner than the first version. If I understand your last post, I agree that in the CompanySeasonProductTemplate table, you could create a composite index on CompanyID, SeasonID, ProductCategoryID with only CompanyID being NOT NULL. This way you can have:
Company A, null, null
Company A, Spring, null
Comapny A, Spring, WrappingPaper
Company A, Fall, WrappingPaper
Company A, null, WrappingPaper
but no duplicates.
I'm still unclear what you will do next year if Company A wants a Spring WrappingPaper event with a LeadTime different than this year's.
One other question. Currently the CompanySeasonProductTemplate table stores both CompanyID and EventID as foreign keys to the Event table. It seems that EventID is the only field needed for this relationship. The CompanyID is unecessary in the Event table, it can be derived via the EventID in queries.
Are we having fun yet?
-
These are just templates, not FINALS...so, year to year doesn't matter.
Also, companies can insert thier own events, which do not HAVE to go into the Template table, but could be used later in project events. Templates are just that...templates, they can then be adjusted. Actually, PrjectEvent needs a LeadTime as well, and it cannot be NULL, and it will be the value that is updated on a project by project basis... Make sense?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply