Hi, I'm trying to create a production planning file which it checks for available week space for the production to start.

Planning file contains "production start date" which falls under every Friday.

Sound similar to appointment scheduling.

Can production for a particular production record be broken up over more than one week? How often can the Production Line Capacity change?

Yes, pretty much like appointment scheduling i guess...

Production Line Capacity doesn't change. It is always a fixed number and yes depends on type of order, it can be broken up over more than a week.

Example: My week capacity is 500 pieces and available capacity is 200 for this week.

I receive order for 300 then 200 will be assigned this week and rest 100 on next week.

I haven't worked on a scheduling system for a couple years, so I might be missing something. but here's my thoughts.

I always like to start with an ER diagram showing how different tables relate, so below is an ER diagram showing my understanding of your system.

An order can have many Production Requests (for different items). The request would be set with a requested production date. Each Production Request then might be broken down into a couple pieces, which are queued in the week where there is room. Each Production Week would then show all queued items for that week.

There would need to be a little scripting to turn a Production Request into one or more Production Queue records.

Hope that helps.