Relationships2

[Saddletramp]

What is the advantage / disadvantage of using real names (Company Name / Employee Name) as opposed to Company Code or Employee Code as a means of forming a relationship between two tables? I would like my relationship from my Parent table to flow down and automaticly create the link in the child table (Less data entry less mistakes). I want to bring in other information like week number, manager name, forman name, and company name. I want thes all to flow down to the child table But if the relationships to each field are not made the information will not flow down to the liked tables.

My solution was to create a single meaningful code

That links and flows down with the links when a new child file is reated

IE...

The Company code is the first three caricters of the code, next 3 caricters are the manager, next three are the forman, and the last two are the week giveing me a 10 caricter code I can break down as needed in each table.

Is there a better way??? Opinions welcome

The Tramp

[mr_vodka]

What happens when you have more than one user or company with the same name? Also another issue can be what happens if someone gets married or divorced and changes their name? All the records peviously associated with their name no longer does with the new ones going forward. If there are unique number keys then all you have to do it change it in one place.

[Saddletramp]

Come on guys I need some feedback on this issue. I have someone who insists on using names rather then codes and I'm trying to prove a point!

Thanks

The Tramp

[comment]

I am not sure what point you are trying to prove. Using names is just as bad as using the code you have described. The core relationships should be based on MEANINGLESS IDs, divorced from ANY attribute values. That is hardly a matter worth discussion.

The best way to achive this is to have an auto-entered serial number field CompanyID in the Companies table, and a corresponding number field CompanyID in Employees.

[David Jondreau]

And use lookups to populate your child fields, if that's really necessary.

If you can GUARANTEE uniqueness of an constructed ID (ala Ray Cologon's uID), I think that's ok. But the OP's method doesn't meet that test.

[comment]

IF necessary. It's hard to imagine such need in a Company - Employees relationship. And speaking of that - how would a 'week number' be an attribute of a company?

BTW, I don't think a "constructed" or a random UID can be GUARANTEED to be unique - at best, it can reduce the chances of a duplication to "negligible". Of course, negligible is a subjective term.

I haven't examined Ray's uID technique, so let me take a generic example of combining SystemNICAddress, RecordID and CurrentTimestamp. This seems to be a smart combination, since each computer is unique by virtue of its NIC address, and each record - even if created on the same computer within the same second - is unique by virtue of its RecordID.

Scenario:

After a prolonged use, involving the creation of many records AND ALSO DELETION of some of them, the solution is moved to a fresh clone. Old records are imported - but the deleted records mean that the system will now repeat some of the more recent RecordID's. The NIC addresses are of course the same as before, so all that's left is for some naughty employee to turn back the clock on their laptop (perhaps to fool the demo of some downloaded game), and...

[David Jondreau]

...and the user would have to create a record at the same second as the record with the matching Record ID. Impossible, no. Highly improbable, yes.

Regardless, I would argue that once you start importing records, even the auto entered serial number is at risk.

[comment]

The probability increases rapidly if the employee spends the day having meetings and taking notes, and only enters records when he gets back to the hotel at the end of a business day. Say 60 records in an hour - that's a 1/60 probability. True, you have to multiply that by the chance that the deleted records belong to the same employee, but...

In another scenario, this traveling salesman always takes a fresh clone with a reduced set of records when he goes out on a trip, so he's ALWAYS recycling RecordID's. And he's the one responsible for bringing in new customers, so most records ARE his to begin with. Add a faulty battery that keeps resetting his clock to 1/1/1970, and you have practically guaranteed duplicates.

I would argue that once you start importing records, even the auto entered serial number is at risk.

Not really (if handled properly). The difference is that with serial numbers the vulnerability is limited to the import/migration process. The danger with UID is the false sense of security that you can create records anywhere and import them without thinking twice - because hey, we have a UID, right?

[LaRetta]

What's the prob, Tramp? Didn't JOhn say enough to convince about not using meaningful keys? I would think so! Picture what would happen to your relationships if the following changed (based upon your OWN information):

• Week number: A week number this year is not the same timeframe as a week number 3 years from now.
  
• Week number: Oooops, the secretary typed the date wrong and it inserted the wrong week number wrong when creating the record and many children and grand-children were also created, incorrectly inheriting that wrong week number. Now spend a long time correcting the problem (if you can).
  
• Manager name: Well, he was fired and was replaced with a new manager and she needs to inherit all his customers. But now HIS name is implanted in all the relationships and their children and grand-children. New records have HER manager name in them, great ... but because his exists still in many records, all sub-summary reports produce TWO independent groups of sales instead of the one as it should be. Bummer.
  
• Foreman name: same same
  
• City, State: Dang if they didn't move their office to the next city.
  
• Phone: same same

Do not come up with a great idea on your own with this one. Unique IDs are a staple and a standard and for very good reason. I am pleased you are taking the time to find out before you've created a nightmare which would take months to clean up. I can tell you many horror stories about Developers or businessmen who insisted on their own meaningful keys. Proper design and meaningless keys is the ONLY way to go here.

LaRetta :wink2:

[David Jondreau]

Not really (if handled properly). The difference is that with serial numbers the vulnerability is limited to the import/migration process. The danger with UID is the false sense of security that you can create records anywhere and import them without thinking twice - because hey, we have a UID, right?

Well, I'm not saying that. I think you have an excellent point about how to corrupt the uID system if importing is a factor.

I am saying a uID is as secure as an auto-enter serial number for producing unique values. If one is importing with uIDs, one has to handle it correctly, just as you do with auto enter.

I've never developed a solution that required regular imports. What's a good method for handling the 2 salesmen on the road with data that gets imported into a master db? A simple auto-enter serial doesn't seem like it would work.

[comment]

I am saying a uID is as secure as an auto-enter serial number for producing unique values.

Suppose I agree with that. What then would be the purpose of bothering to set up a UID - unless you plan to import records without any further precautions?

The intent of UUIDs is to enable distributed systems to uniquely identify information without significant central coordination.

http://en.wikipedia.org/wiki/UUID

The uID system allows you to efficiently create unique key values for records in your databases, safe in the knowledge that duplicate values will not be created, even if records are generated simultaneously in multiple stand-alone copies of your database. The resulting record sets can be subsequently merged without fear of key conflicts.

http://www.fmforums.com/forum/showpost.php?post/286535/

(Emphases in both quotes are mine.)

What's a good method for handling the 2 salesmen on the road with data that gets imported into a master db?

One method (probably not the only one): Import parent records, mapping the source ParentID into a ImportedID field, while assigning a new serial ParentID. Import child records. Use a side-chain relationship Parent::ImportedID = Child::ParentID to replace the foreign key in child records with the new and correct ParentID.

[Saddletramp]

In my scenario (reality) everything is based on one year. Everything is generated from here (No Imports from outside sources). Jobs are generated from Customer (Company) a generated 3 character autogen. code (From the company Table). Next is the district autogen. 3 character code (from the District table) Next is the type of job, Next 2 generated characters (from the Job table). Next the State , Next 2 characters autogen. From the state table. Next is the Manager of that job autogen 3 character from Employee table, Next is the Forman autogen 3 character from Employee table, Then the week number (2 character format) from the dropdown calendar Stored date field generating the week number from the week number Application in the calculated field screen. Each step generates a two or three character number set (IE. If (xxx)=1;”00”& xxx; xxx). No matter what each step generates If It doesn’t meet the 18 character designation at the end something is wrong. This 18 character code is set to an exclusive field no duplicates, if a duplicate comes up it rejects the code.

Company District JobType State Mger Forman Week

(XXX) (XXX) (XX) (XX) (XXX) (XXX) (XX)

Please tell me how this can produce duplication problems. As for the manager being fired or quits all I have to do is bring out the Mid(xxx)8;3) and replace it or make it equal to : then reconstruct the code.

Basically the generated code is meaningless but it produces a given number of characters and placed in a given position, and can be broken out and used as information.

I welcome you to point out the flaw in my logic. This is a learning process but I have used it for years with out problems.

Thanks

The Tramp

[Saddletramp]

[*]Week number: Oooops, the secretary typed the date wrong and it inserted the wrong week number wrong when creating the record and many children and grand-children were also created, incorrectly inheriting that wrong week number. Now spend a long time correcting the problem (if you can).[

The field is linked to a Test table listing the last day of the week (Sat. Date) If the date doesn't match, it rejects the date.

/li][*]Manager name: Well, he was fired and was replaced with a new manager and she needs to inherit all his customers. But now HIS name is implanted in all the relationships and their children and grand-children. New records have HER manager name in them, great ... but because his exists still in many records, all sub-summary reports produce TWO independent groups of sales instead of the one as it should be. Bummer.

NO matter how you identify this person when the change comes you still have to make the changes across the board.

[*]Foreman name: same same

[*]City, State: Dang if they didn't move their office to the next city.

[*]Phone: same same

All this information is in the company table linked anywhere in the database by the extracted company code built in the job code (first there characters, or Left(XXX;3)linked to a copy company table will give you any information about the company you want. And if the company moves just change the information in the company table.

Next

The Tramp

[comment]

I didn't say YOUR code can generate duplicates - although I am quite sure it can. I just don't know which description to refer to, since in your original post the code has 10 characters, and now it has grown to 18.

Using your original description, a duplicate is generated when 2 or more records are generated in the same week for the same company, with the same manager and foreman. Your recent description mentions "2 generated characters (from the Job table)" - but I don't know how these are generated and what protection, if any, is there against 2 users generating the same 2 characters.

In any case, the duplication problem is very minor compared to the real issue here - the fact that your code is meaningful and "can be broken out and used as information". [color:red]If the code can be used as information, then it cannot be used for relationships. Your own example demonstrates this perfectly:

As for the manager being fired or quits all I have to do is bring out the Mid(xxx)8;3) and replace it or make it equal to then reconstruct the code.

And once you have done that, all the Job's children records become orphans.

All the information you are trying to pack into the code is readily available from the related records directly. So a meaningful code is not only unsuitable, it is also unnecessary.

[Saddletramp]

In any case, the duplication problem is very minor compared to the real issue here - the fact that your code is meaningful and "can be broken out and used as information". [color:red]If the code can be used as information, then it cannot be used for relationships. Your own example demonstrates this perfectly:

As for the manager being fired or quits all I have to do is bring out the Mid(xxx)8;3) and replace it or make it equal to then reconstruct the code.

And once you have done that, all the Job's children records become orphans.

All the information you are trying to pack into the code is readily available from the related records directly. So a meaningful code is not only unsuitable, it is also unnecessary.

[comment]

If you don't change the code, then it cannot be used as a source of information - because the information might have changed since the code was created. So then the question becomes: why bother brewing this complex concoction, if you can't drink it?

If a manager is assigned to a job via a relationship, the manager's FULL name (and any other detail) can be viewed simply by placing the related field/s from Employees on the Job layout. And if the manager is replaced, or he changes his name, etc., the information is updated automatically.

[Saddletramp]

The code changes on a weekly basis the reason for the week number at the end of the code. If the manager changes as well for that week or there are more than one manager to a job I can Identify via the code which managers... forman... were associated with a job based on the Company code in the beginning of the code. I can tell what vehicles were used based on the Forman who checked them out for the week. All expenses flow back to the company ID. The serial generated ID number that builds the code never changes for that Person, Vehicle, Company.

Maybe we are fight the same battle here. My information tables are Identified by a meaningless Serial gen. ID number. The employee's name is associated with that number. Same with the company in the company table. Then I use these Individual codes to create the JOB Code we are debating. The "Job Code" never sees the individual information (Co. Emp...)tables. The Job code linkes the Job table to the Equip. Use table and so on with other tables associated with "that" Job.

The Tramp

[LaRetta]

I would never change a code. Once any thing changes in a code it becomes a new job

Ah. But if you have a code where (for example) state is always the first two characters and a customer moves to a new state ... your STAFF will still try to depend upon that code telling them something about the customer and that is bad. So they will see AL and still think it's a customer from Alabama; if they think someone is from CA, they will start their search for CA and ...

If staff thinks it has meaning, and the meaning has changed, then the code is MEANINGLESS. Since it can be nulled very easily if anything changes, why use it at all??? If their state changes, change their STATE! You can always depend upon it because it is the origin!

oh what's the use trying. A man convinced against his will is of the same opinion still.

Well, I truly wish you well with it; truly.

[Saddletramp]

Ah. But if you have a code where (for example) state is always the first two characters and a customer moves to a new state ... your STAFF will still try to depend upon that code telling them something about the customer and that is bad. So they will see AL and still think it's a customer from Alabama; if they think someone is from CA, they will start their search for CA and ...

If staff thinks it has meaning, and the meaning has changed, then the code is MEANINGLESS. Since it can be nulled very easily if anything changes, why use it at all??? If their state changes, change their STATE! You can always depend upon it because it is the origin!

Well, I truly wish you well with it; truly.

Edited April 18, 2008 by Guest

[comment]

Maybe we are fight the same battle here.

I don't know. I thought we have established that the code cannot be relied upon to supply information. Yet you keep referring to it for this purpose.

If you find it convenient, that's fine: make yourself an unstored calculation using the same elements, and put it somewhere on a layout where you can see it. Just don't use it for relationships. Do the exact opposite: use relationships to put this code together, and to keep it up-to-date at all times.

[Saddletramp]

I don't know. I thought we have established that the code cannot be relied upon to supply information. Yet you keep referring to it for this purpose.

If you find it convenient, that's fine: make yourself an unstored calculation using the same elements, and put it somewhere on a layout where you can see it. Just don't use it for relationships. Do the exact opposite: use relationships to put this code together, and to keep it up-to-date at all times.

What you have just said is, I think, what I am doing I have a calculation that puts the companyId & What ever ID &... to create my job code the job code only links what that job is associated with. On the layouts you never see the job code you see what the code broken down represents.

The Tramp

Sorry, I misread "unstored" as "unsorted". I am storing the calculation. But, If I can't use the Company code Say "101" to link "South Beach" TO the The "Equipment use" Table That links truck number "235" for the Chevy truck described in the equipmnet table, How can I link it.

Edited April 18, 2008 by Guest

[comment]

What you have just said is, I think, what I am doing

I don't think so (if I understand your description correctly). The main difference is that your code is STORED in the Jobs table, and doesn't update when one of the elements used to construct the code is modified.

On the layouts you never see the job code you see what the code broken down represents.

What difference does it make? If the information on the layout is based on the stored code, then it cannot be up-to-date. You cannot have it both ways: either the code updates (and breaks the relationships based upon it), or it doesn't update (and cannot be trusted as a source of information). Read again the sentence in bold red a couple of posts above.

If I can't use the Company code Say "101" to link "South Beach" TO the The "Equipment use" Table That links truck number "235" for the Chevy truck described in the equipmnet table, How can I link it.

Could you rephrase this question? I am not sure what's a table and what's a field in your description.

[Saddletramp]

If I can't use the Company code Say "101" to link "South Beach" TO the The "Equipment use" Table That links truck number "235" for the Chevy truck described in the equipmnet table, How can I link it.

Could you rephrase this question? I am not sure what's a table and what's a field in your description.

[comment]

Of course you link the CompanyID in Companies to the CompanyID field in a related table. The point is that the CompanyID field in Companies is a number field, with the option to auto-enter serial number turned on*. This number is meaningless, and does not rely on any attribute of the company.

---

(*) And so would be the VehicleID in the Vehicles table.

Edited April 21, 2008 by Guest

[Saddletramp]

Of course you link the CompanyID in Companies to the CompanyID field in a related table. The point is that the CompanyID field in Companies is a number field, with the option to auto-enter serial number turned on*. This number is meaningless, and does not rely on any attribute of the company.

---

(*) And so would be the VehicleID in the Vehicles table.

OK, The Company ID is the first three caricters of My Job Code as well as the rest of the code is constructed of Meaningless ID numbers related to a particular job, when the code changes it becomes a new job. I can break out the Company ID and link it to the company table if needed and get all Company Information.

The Tramp

[comment]

I think we are going in circles here. If the Company ID is the first three characters of anything, then it's not meaningless.

[Saddletramp]

I completely understand the purpose of not using a meaningful, (Personal name, Company Name...)or any variable information as a link. This is a basic rule of Database construction. But, once the basic structure is met, be it a Serial number, a code pulled out the sky, or an assigned number or what ever... it will represent the underlying information for ever. There is no logical reason why that number cannot construct a usefull connection to other related information, because once there is a change in the constructed code it is a new code no longer related to anything in history it will create its own.

The Tramp

[comment]

I am not sure I understand the last sentence, but if you mean that as long as the ID does not change, AND is guaranteed to be unique, no one cares how you generated it, I'll agree.

[Saddletramp]

"as long as the ID does not change, AND is guaranteed to be unique, no one cares how you generated it, I'll agree. "

That is the bottom line.

The Tramp