Found anything Missing?

[prabha_friend]

Please report. Thanks

 

[Pat Hartman]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

 

[Pat Hartman]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

 

[prabha_friend]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

one of my schoolmate's relations is running a small Dairy. He asked me to develop an inventory cum software for him. I choose MS Access as the Base and escalate to further technologies if needed. This is my ever Database I am developing for a Dairy. Upon thinking. I have derived the following entities:
Customer (Living in a Particular Road Line of a City)
Item (5 items in general: Milk, Curd, Butter, Ghee and Khoa)
Subscription (Can be switched, Paused and Closed)
Adhoc Request (Can be done on the above 5 listed items)
and Finally Delivering the Same.

 

[prabha_friend]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

Why this much fast

 

[prabha_friend]

Main Subscription Form:

Delivery Drafts:

Why I call it Drafts:
Because its the form which loads all the deliverables from the database and make it acknowledged after Delivery.

 

[prabha_friend]

Moment is nothing but a combination of Date and Time.

 

[prabha_friend]

SELECT Customer.ID, [DD] AS Delivery_Date, Subscription.Session, Customer.NameVal, Item.ID, Item.NameVal, Subscription.Rate, Nz((SELECT LAST([Volume_Switch].[New_Volume]) FROM [Volume_Switch] WHERE [Subscription].ID=[Volume_Switch].[Subscription_ID] ),[Subscription].[Volume]) AS Delivery_Volume
FROM Item INNER JOIN (Customer INNER JOIN Subscription ON Customer.ID = Subscription.Customer) ON Item.ID = Subscription.Item
WHERE (((Subscription.Moment)<=[DD]) AND ((Subscription.ID) Not In (SELECT [Closure].[Subscription] from [Closure]) And (Subscription.ID) Not In (SELECT PAUSE.[Subscription] from pause where [Pause].[Subscription]=[Subscription].[ID] and ([DD] between [Pause].[From] and [Pause].[To]))))
ORDER BY Customer.ID, Subscription.Session DESC;

 

[plog]

Moment is nothing but a combination of Date and Time.

That explains the datatype but not what the data represents. What does that date/time represent in the context of each table that field appears in?Why is it necessary?

Lasty, and most importantly, you're just throwing things at us. Posted a schema, thoughtful notes were giving, you briefly and very tangentially addressed them and then threw a form at us, without waiting for a response you then threw a query at us without reason or context. I can't tell if you are showing off what you have done or genuinely want feedback and assistance.

 

[prabha_friend]

That's all the Database Has.
Just thought having a column to denote the birth of each record. For example:
The Shop owner may say: "Yeah we have covered that area from this year onwards"
Customer Anniversary offer
Now. It's has become an habit of mine to add a Moment column for every Key entities...

 

[plog]

I suggest you go to Pat Hartman's original numerated response (Post #2) and address what she brought up number by number. Either tell her that you agree with each and every point, or explain why you disagree. Go point by point to each of her numbers in that post. Then we can move on to forms and queries.

 

[prabha_friend]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
I want to create the Entities as we see in the real word. For example I also feel that the Table name is sufficient to know it is a different entity's ID. But the problem is: we have to address the table name also in the queries. For example: Item.ID and Subscription.ID like that. I am fine with that.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
Yes. Will be useful while upsizing to SQL Server.
3. your names are inconsistent. Subscription vs Subscription_ID
Yup. My Mistake. Will be rectified.
4. the tables Line and Item have identical attributes?
But those two are completely different entities.
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
Moment is nothing but a combination of Date and Time.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
I feel it would be better to a have solid details carved in the final Bill (Delivery). Since I am keeping the Customer and Item ID in the delivery itself I excluded that. Yes, I also feel its not perfect.
7. I have no clue what this schema is for. You have given us no context.
one of my schoolmate's relations is running a small Dairy. He asked me to develop an inventory cum software for him. I choose MS Access as the Base and escalate to further technologies if needed. This is my ever Database I am developing for a Dairy. Upon thinking. I have derived the following entities:
Customer (Living in a Particular Road Line of a City)
Item (5 items in general: Milk, Curd, Butter, Ghee and Khoa)
Subscription (Can be switched, Paused and Closed)
Adhoc Request (Can be done on the above 5 listed items)
and Finally Delivering the Same.

 

[prabha_friend]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

Yes. I agree and will stop putting likes

 

[prabha_friend]

That explains the datatype but not what the data represents. What does that date/time represent in the context of each table that field appears in?Why is it necessary?

Lasty, and most importantly, you're just throwing things at us. Posted a schema, thoughtful notes were giving, you briefly and very tangentially addressed them and then threw a form at us, without waiting for a response you then threw a query at us without reason or context. I can't tell if you are showing off what you have done or genuinely want feedback and assistance.

Please understand that I have attended the schools only upto 10th standard in India. Everything I read, build and Share is being done out of sheer curiosity and Passion that I share on my works...

 

[prabha_friend]

I suggest you go to Pat Hartman's original numerated response (Post #2) and address what she brought up number by number. Either tell her that you agree with each and every point, or explain why you disagree. Go point by point to each of her numbers in that post. Then we can move on to forms and queries.

Done Plog. Waiting for your thoughts of my forms and queries...

 

[Mark_]

Backing up two steps, you will need to be very CLEAR on what you intend to have this database DO. "Dairy" doesn't change that this looks like a scheduled delivery of goods system. If this is true, what outputs do you need?

 

[The_Doc_Man]

As often as you have posted here, and as long as you have been on this site, you should know that just posting a relationship diagram does not tell us ANYTHING about the goals, rules, and methods of the business it represents. A verbal overview of the intent of the DB would have been helpful. Prabhakaran, you KNOW we will answer you if and when we can - but there is no need to make it hard for us to know what you intended.

 

[prabha_friend]

Backing up two steps, you will need to be very CLEAR on what you intend to have this database DO. "Dairy" doesn't change that this looks like a scheduled delivery of goods system. If this is true, what outputs do you need?

There are two places the user touches my form. One to know what all the Deliveries are scheduled for today and todays' requests list. But finally, he has to "Mark it Delivered" what all are items actually delivered today. The same data. First time he "reads" second time he "writes".

 

[Pat Hartman]

Yes. I agree and will stop putting likes

It is OK to like posts but the like should be meaningful. You "like" a post because it gave you the answer you needed. There is no need to simply like every response everyone makes.

Now. It's has become an habit of mine to add a Moment column for every Key entities...

"moment" is not a meaningful name. Your explanation says these fields represent CreateDT. Why not name them CreateDT? I would have instantly understood what they were for and Plog would have also. Don't make others guess. This app is "yours" for the "moment" but at some point, someone else may take over responsibility. That is why I suggested meaningful names rather than ID and also consistency in naming. That is ALWAYS how software development works unless the app is strictly and only for personal use.

1. You are not obligated to take expert advice if you think you know better.
4. I assumed they were different but the point is, I have no clue what data those tables will contain. Therefore, the attribute names are meaningless. When you assign attribute names, the only reason to ever duplicate a name is when the attribute holds the identical type of data in all instances. CreateDT is an example. It holds the date "this record" was created whenever it appears. It doesn't mean the value will be the same in every place. I'm pretty sure NameVal holds StreetName in one case and ItemName in the other and therefore the two names should not be identical. Names do not need to be long, nor should they be. The shorter the better but you need to avoid all but the most common abbreviations. Abbreviating DT for date will be understood in context, especially when the DT suffix is used in many other places.

I feel it would be better to a have solid details carved in the final Bill (Delivery). Since I am keeping the Customer and Item ID in the delivery itself I excluded that. Yes, I also feel its not perfect.

There is a difference between "not perfect" and wrong. This relationship is outright wrong. No relationship should allow the ambiguity I mentioned. Therefore, this relationship needs to be fixed, NOW, before moving on. Now that I have a better idea what the application actually does, I can suggest a way to fix the problem.

The subscription table is necessary so you can make repeated deliveries of the same products to a specific customer. However, a subscription is not an order and you have used this table for two purposes. Request is the Order. That is what will be delivered for a particular order. So the solution is that you need to have a weekly/daily (which ever turns out to be best for your process) query which reads the subscription table and creates the Next Request (order). The fulfillment part comes ONLY from Request and never from Subscription. That solves the problem of Item being a child of two tables. The Item table which you have only one version of should actually be THREE tables. a. Item - this is the table that defines each item the dairy sells. It includes the volume in the container as well as the unit price per item. b. SubscriptionItem - the Items that are specified for this standing order. b. RequestItem - the Items that will be delivered for this order. Also, the details regarding the Item, don't belong in the subscription or Request tables. You have both relationships backwards. Subscription should contain a startDT and an EndDT so you know when the subscription started and when it ended. It should also contain the frequency. Should this item be delivered weekly, daily, bi-weekly. Again, whatever makes sense for the dairy. I'm remembering back to my childhood when we had milk delivered to our house. We had a quart of milk delivered Mon, Wed, and Fri every week and a pint of cream every other Wed. So you might need several fields to fully describe the frequency. The Volume belongs with the Item description in the Item table and a quantity belongs in the Subscription and RequestItems tables. So, in Item, the volume is 1 quart or liter. And this record also includes the price with a StartDT and EndDT. Then the quantity in the other two Item tables says deliver 2 quarts or 1 quart at the specified frequency.

"Rate" is a word generally used to represent a percent so you would never use it to represent the price in Rupees or whatever currency you are using. You would use Cost or Amount.

There are other changes I would suggest but that should give you a lot to think about.

You have started making forms and queries WAY TOO EARLY. Your schema is not even close to being correct or representative of the real world. Don't be so anxious to get to the "good" stuff. Make sure the foundation is sound before you start hanging the curtains.

 

[prabha_friend]

It is OK to like posts but the like should be meaningful. You "like" a post because it gave you the answer you needed. There is no need to simply like every response everyone makes.

"moment" is not a meaningful name. Your explanation says these fields represent CreateDT. Why not name them CreateDT? I would have instantly understood what they were for and Plog would have also. Don't make others guess. This app is "yours" for the "moment" but at some point, someone else may take over responsibility. That is why I suggested meaningful names rather than ID and also consistency in naming. That is ALWAYS how software development works unless the app is strictly and only for personal use.

1. You are not obligated to take expert advice if you think you know better.
4. I assumed they were different but the point is, I have no clue what data those tables will contain. Therefore, the attribute names are meaningless. When you assign attribute names, the only reason to ever duplicate a name is when the attribute holds the identical type of data in all instances. CreateDT is an example. It holds the date "this record" was created whenever it appears. It doesn't mean the value will be the same in every place. I'm pretty sure NameVal holds StreetName in one case and ItemName in the other and therefore the two names should not be identical. Names do not need to be long, nor should they be. The shorter the better but you need to avoid all but the most common abbreviations. Abbreviating DT for date will be understood in context, especially when the DT suffix is used in many other places.

There is a difference between "not perfect" and wrong. This relationship is outright wrong. No relationship should allow the ambiguity I mentioned. Therefore, this relationship needs to be fixed, NOW, before moving on. Now that I have a better idea what the application actually does, I can suggest a way to fix the problem.

The subscription table is necessary so you can make repeated deliveries of the same products to a specific customer. However, a subscription is not an order and you have used this table for two purposes. Request is the Order. That is what will be delivered for a particular order. So the solution is that you need to have a weekly/daily (which ever turns out to be best for your process) query which reads the subscription table and creates the Next Request (order). The fulfillment part comes ONLY from Request and never from Subscription. That solves the problem of Item being a child of two tables. The Item table which you have only one version of should actually be THREE tables. a. Item - this is the table that defines each item the dairy sells. It includes the volume in the container as well as the unit price per item. b. SubscriptionItem - the Items that are specified for this standing order. b. RequestItem - the Items that will be delivered for this order. Also, the details regarding the Item, don't belong in the subscription or Request tables. You have both relationships backwards. Subscription should contain a startDT and an EndDT so you know when the subscription started and when it ended. It should also contain the frequency. Should this item be delivered weekly, daily, bi-weekly. Again, whatever makes sense for the dairy. I'm remembering back to my childhood when we had milk delivered to our house. We had a quart of milk delivered Mon, Wed, and Fri every week and a pint of cream every other Wed. So you might need several fields to fully describe the frequency. The Volume belongs with the Item description in the Item table and a quantity belongs in the Subscription and RequestItems tables. So, in Item, the volume is 1 quart or liter. And this record also includes the price with a StartDT and EndDT. Then the quantity in the other two Item tables says deliver 2 quarts or 1 quart at the specified frequency.

"Rate" is a word generally used to represent a percent so you would never use it to represent the price in Rupees or whatever currency you are using. You would use Cost or Amount.

There are other changes I would suggest but that should give you a lot to think about.

You have started making forms and queries WAY TOO EARLY. Your schema is not even close to being correct or representative of the real world. Don't be so anxious to get to the "good" stuff. Make sure the foundation is sound before you start hanging the curtains.

"Should this item be delivered weekly, daily, bi-weekly. "
Yup. Frequency must be there. Hi Pat, Will revise the schema and share soon. Thanks for such an extensive analysis.