Question for database developers having an argument and need some help
 

Me and a buddy are having an argument about how a database should be structured please post below which way you believe is the best way from the 2 choices below.


First a little info about the database


The database will have subcontractors in it and thier employees to track sales by subcontractor and by employee.




Option 1

Every subcontractor get an id
Example: 001

Every employee gets a id in a second field
Example: 1

So first database would look like this
001-1
001-2
for the tracking of sales



Option 2

Make id #'s for each employee without a field for the sub.

Example

1001
1002
1003
1004
1005
1006
1007

In this option each # can be assined to a diffrent subcontractor



So which way do gfy people think is best?

you win.

purely because you posted on gfy about it, its the principleof the damn thing :Graucho

Lol choose which way you think is best wont tell which side im on :)

Depends a bit on how big your tables are likely to be, but in general I'd go with the first option.

Functionally I don't see as it makes any difference. You can write your data entry user interface or any report to display the employee & contractor numbers either combined or separated. Programming might be easier or harder for different things depending on the choice, but the end result isn't any different.

As far as performance, I'm guessing it also won't make any difference. It depends on the size of the database of course, but if you're talking under a hundred thousand records, if the database is well written, performance should be blazingly fast on modern hardware regardless of the data structure. If it were an enormous database, then option 1 would have a performance edge for certain reports (e.g. groupings by subcontractor...easier to have a distinct field in option 1 rather than have to calculate the grouping based on a combined field like optio n2).

From a common sense perspective, I think I'd go with option 1. It just feels intuitive to have separate fields for different types of data like that. I mean to take option 2 to an absurd level, you could toss the name into the ID as well, and make them 1001_John_Smith and 1002_Jane_Dough, and it woud still make no difference functionally and little difference in performance for a small database, but it just makes no sense, and would make for extra programming work parsing the ID's for different purposes (e.g. sorting by last name).

Assuming there is a one to many relationship between subcontractors and employees, you need two tables:

subcontractors
employees

the subcontractors table and the employees table should both have primary keys, and the employees tables should have a foreign key to the subcontractors table primary key.

ie:

subcontractors(id,name,description)

employees(id,subcontractors_id,name,whatever)

database normalization 101.

SO option 1 ?