Hi There,

I am new to filemaker and am creating a database for my business. Our business involves selling cab glass for heavy machinery (jcb's etc)

Every manufacturer and model of machine has it's own record in the "Machine Models" table. Every machine has only one cab, but one cab can fit many machines. Each cab is made up of up to 10 pieces of glass (items). One piece of glass (item) can fit multiple cabs.

I have asked advice on another forum and feel like I am getting somewhere:
Help needed with tables and relationships . Using FileMaker Pro . Forum . FileMaker Forums

My most recent revision would be to have the tables like this:

http://i205.photobucket.com/albums/b...7/Tables-1.jpg

Can anyone help me out.

Thank you

modelling these sort of problems can be tricky especially if this is an early attempt
FWIW I got around 6 tables its not trying to model precisely what you originally posted but
optional:-Machine Types identifies the type of plant eg back hoe loader, dump truck, mini digger and so on.. not needed but can be a way of filtering your data when you have a customer who is not certain what type of machine it is
Machine Manufacturers identifies the maker of the plant eg JCB, CASE, Kubota, Caterpillar
Machine Models identifies a specific make of equipment eg JCB 5CX
Model Assembly is the equivalent of your cab entity, I called it assembly in case there is other stuff that you may stock overtime. model assembly has a period from / period to to allow for a change in the components of a specific assembly over time (eg the 5CX from launch had a build X cab, in 2009 the cab was redesigned and requires a different parts list. if your manufacturers don't pull these tricks then you can eliminate the period from to and arguably fold the assembly inot the machine models table if you don't think you will ever need to support multiple assemblies for a specific machine
Parts identifies specific parts made / sold by a manufacturer
optional: part alternatives provides a mechanism for equivalance (eg a JCB 856988 is equivalent to a CASE 0877965675) if you don't have this problem then you can omit this table
AssemblyDetails identifies what parts are used in what assembly and optionally how many. again if there is only ever one of any one part per assembly then quantity is optional


however that is an opinion of what you could require. ultimately it comes down to what you need to represent. ferinstance if you only only ever fitted OEM parts (ie wouldn't replace with a functional equivalent from another manufacturer then you don't need part alternatives. ultimately its your data model its up to you to understand the model and use it appropriately.

the attached drawing isn't a fully fledged data model, its an example of a structure, not all columns are present (ferinstance I'd probably want some sort of free form annotation of the model assembly (eg assembly 978687 is the civilian 5CX, assembly 7698769876 is the combat engineer 5cx and so on)

Thanks very much for your reply. The part about a change in year for a particular model is very relevant to us. I will take some time to digest this and get back shortly. Cheers

Your idea of splitting Machine Type and Machine Model into different tables is great. I am also going to create another table called model groups (series numbers) .

I think this is a good idea incase we ever need to store information e.g. notes, images regarding a make, type or group. Also, if a customer only ever purchased glass for say a jcb it may be easier to filter only JCB glass at this level. Or if we ever did a campaign for jcb glass, we could filter out all the jcb glass from here. I suspect it may be quicker to search if these entities are in their own table. Am I correct about this or am I talking rubbish?

Is there anything wrong with having the cab assembly tables having multple records with the same CabID? Would it be better or feasible to split every cab into it's own table?

the basic rules for designing tables in a relational database are covered by 'normalisation'
Fundamentals of Relational Database Design -- r937.com
and
The Relational Data Model, Normalisation and effective Database Design
are two good sources, but there's plenty of others.
I don't think the model groups entity is neccesarily the right approach, unless you know in advance what serial numbers comprise what build standard. I would expect the manufacturer to have some other mechanism to identify a build standard, or they would retain the serial number ranges within their own system(s). if they release that information then fine, by all means store it.

in the relational DB world normally you have a primary key for every row (something that makes that row unique) however in the real world there may be several cabs for a specific model. or there could be equivalancies (eg say a manufacturer released a set of tinted windows in place of clear glass). I don't know enough about glazing in mechanised plant (and I'm not sure I want to know more), but in other industires its not unknown for a manufacturer to do "specials" eg Makita did a production run of white framed drills instead of their more usual blue drills to celebrate / mark some age milestone. the drill remained the same but was different. if your suppliers do the same you need a mechanism to represent that

seperate tables for what is essentially the same information is never a good idea. every time there is a new cab, it requires development staff time and effort. get the design right firts time means there is little future involvement from development (IE you) as users control their own data.

bear in mind a piece of glass is just that
its a part supplied by a manufacturer, heck it may even be supplied by more than one manufacturer
as a part it has specific dimensions which make it unique
it could be used in multiple locations (either in the same model vehicle or in other models from the same or differnt manufacturers).

to handle part equivalencies you either have to rely on the manufacturers part number being unique (possibly with the manufacturer ID) or devise another mechanism. Id suggest you have your own internal partnumber (it doesn't have to be seen by the outside world, or even be seen inside our company. providing you have a part equivalence it doesn't matter if the manufacturer changes their part ID.

the problem of showing your internal system unique ID is that users can start to use them in the real world where they have little or no relevance personally I would drop the idea of prefixing stuff with 'CAB', especially as the CAB is clear enough given you are using a table called 'Cab Assembly'.

I don't know how your organsiation is structured but there are things you can do ferinstance you associate a drawing with the Assembly entity (or as you currently have it laid out your models table. that assoiciation could be a scan of the relevant parts page or it could be a URL to the manufacturers page. likewise each actual part when displaye on screen could point to a manufacturer info page.

I don't understand the column 'variation'
Calling a column 'year' could cuase problems with the db. I don't use Filemaker, when I last saw it around 10 years ago it seemed a crock of sh1te as far as my needs were concerned but calling columns 'year' does cause problems with other db's. check if filemaker has a list of reserved words which you shouldn't use.

also 'year' isn't very descriptive... im guessing its a production year, but I'd expect a manufacturer to run with a specific build standard for wahterver period they deem neccessary... it coudl be a matter of months it could be a single year, it could be several years. I'd expect your customers to say they are looking for a piece of glass for a JCB 5CX, they may know year of production but may not. so your issue is how do you identify what part the customer actually wants. it may well be that the best way of identifying that part is to refer to the supplier's (in this case JCB) parts system. which means is there any added value in creating a db? what does your proposed design do that existing systems cannot do? what is it that justifies the time and cost in developing your own systems especially as populating the db may be an onerous task and keeping it upto date likewise.

sometimes there are good reasons to reinvent the wheel, but often its far simpler to use a wheel that soemone else already provides for free (or even for a fee)

Thanks again for the reply.

You are correct, we do not have access to Manufacturers serial numbers. We are an original equipment alternative. In this sense, I can see your point about the model groups entity. There could be for example a 300 Series for a JCB excavator and also a 300 Series for a John Deere Tractor. There are likely to be duplicate model groups which isn’t good.

With regard to the primary fields and the field 'year' not being descriptive, I realise this. The image of the tables was just a quick mock up.

Creating a database is a must for us. We are different from a lot of companies. We are actually a grey-goods suplier so we cannot rely on manufacturers systems. We have many suppliers and we manufacture some glass. We have lots and lots of complicated information, such as digital templates, packaging information, delivery information of glass. We are currently working off spreadsheets and are starting to get in a mess with our data.

A model will only ever have one cab assembly but a cab assembly will have multiply makes and models.

I have just realised that the way I am planning on doing this will not work. If you look at the attached file, you can see that each model has a Cab Assemblt ID (fk). This is referncing a Cab Assembly record in the table below. However it it is not refernceing a unique field. I am correct in thinking this cannot work?

I do have another idea.

This is the original way I was thinking of doing it (attached) but I don't think it's a good idea to have multiply similar fields for each record. Is that right?