Question on Database Design
-
This is really troubling me, and I've been beating my head against a wall for 2 and a half weeks attempting to research/find logical arguments for or against this design. I feel it's going to cause trouble (big trouble) and I would like to prevent it from happening. Or I could be completely whacked out, and it's a fine design... However, I'm usually not wrong about this kind of stuff.
I'll try to explain the goal in a general sense. I can't give actual details, or data, but the high level concept is products come in, based on the type and what we do to it, it is charged differently, then products go out.
All of the companies that we deal with, (ship to, bill to, pick up from, etc) are stored in one table (call it "Person" Table).
Each Person can have different classifications, can fill multiple roles in the business. For instance "ABC Corp" can be where we pick up supplies, and who we drop products off to. (Person's can also just be a regular Joe Schmoe Person, not a corporation. Like: average user purchased a USB stick).
We have a table that stores information about the work we performed. We'll call it "Work" table. Usually the path through the data starts at the Work table and needs information from the Person table (usually the name).
If we have a Work event that doesn't require picking up supplies from a company, then we don't want that company available for association to that Work event. This is where the "List" and "ListType" tables come in... If the Person isn't in the "List" of related Persons, then the user can't select them to associate it with a job. The idea was that the "ListListType" table say's if you're List Id 2, than you can choose Person records that match PersonType Ship to or Bill To, but not pick up from. I think this is a bad way to do this, and a bad place to put business logic like that too.
The database design is similiar to the tables below:
-- Holds every possible entity that we could sell to, ship to, interact with in anyway. This table also holds parent child relationships between Persons.
CREATE TABLE Person (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[PersonName] VarChar(250) NULL
)
INSERT INTO Person (PersonId, PersonName) VALUES (1111, 'ABC Corp')
INSERT INTO Person (PersonId, PersonName) VALUES (2222, 'DEF Corp')
CREATE Table Work (
[WorkId] [int] IDENTITY(1,1) NOT NULL,
[WorkType] INT NOT NULL,
[ListId] INT NOT NULL
)
INSERT INTO Work (WorkId, WorkType, ListId) VALUES (123, 5, 2131)
INSERT INTO Work (WorkId, WorkType, ListId) VALUES (124, 5, 2132)
CREATE Table List (
[ListId] [int] IDENTITY(1,1) NOT NULL,
[ListTypeId] VarChar(250) NULL
)
INSERT INTO List (ListId, ListTypeId) VALUES (2131, 1)
INSERT INTO List (ListId, ListTypeId) VALUES (2132, 2)
CREATE Table ListType (
[ListTypeId] [int] IDENTITY(1,1) NOT NULL,
[WorkTypeId] INT NOT NULL
)
INSERT INTO ListType (ListTypeId, WorkTypeId) VALUES (100, 5)
INSERT INTO ListType (ListTypeId, WorkTypeId) VALUES (101, 6)
CREATE Table ListListType (
[ListListTypeId] [int] IDENTITY(1,1) NOT NULL,
[ListTypeId] INT NOT NULL, -- Foreign key to ListType table
[PersonTypeId] INT NOT NULL -- Foreign key to Person Type table (last table)
)
INSERT INTO ListListType (ListListTypeId, ListTypeId, PersonTypeId) VALUES (1, 100, 998)
INSERT INTO ListListType (ListListTypeId, ListTypeId, PersonTypeId) VALUES (2, 101, 999)
CREATE Table ListPerson (
[ListPersonId] [int] IDENTITY(1,1) NOT NULL,
[ListId] INT NOT NULL,
[ListListTypeId] INT Not Null,
[PersonId] INT NOT NULL
)
INSERT INTO ListPerson (ListPersonId, ListId, ListListTypeId, PersonId) VALUES (300, 2131, 1, 1111)
INSERT INTO ListPerson (ListPersonId, ListId, ListListTypeId, PersonId) VALUES (301, 2131, 1, 2222)
-- this table has description of what the Person is. i.e. Bill to, Pick up from person, ship to person, etc.
CREATE Table PersonType (
[PersonTypeId] [int] IDENTITY(1,1) NOT NULL,
[Description] VarChar(250) NULL
)
INSERT INTO PersonType (PersonTypeId, Description) VALUES (998, 'Ship To Customer')
INSERT INTO PersonType (PersonTypeId, Description) VALUES (999, 'Pick Up From Customer')
My thought was to pull the tables that are attempting to regulate which "Work" records can connect to which "Person"s out of the database and put that in the application business layer instead. And even if we don't pull that logic out, then it seems like there's got to be a better, more logical, way to do this...
If you have some time, I would really appreciate any suggestions or thoughts you might have on this design. I really want to know the best way to do this (beyond getting a paycheck). It's one of those things that get in your head and you just can't let it go until you solve it, or at least understand it better. So, any help would be very appreciated.
If you even just have links to advanced db design, that would be helpful also. I've been reading up on everything I can find, and gathering all the information I can, to present a logical argument to them later, but I don't have a enough yet.
Thank you.
-
CREATE TABLE Person (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[PersonName] VarChar(250) NULL
)
create work type table which can hold
ship to , sell to ....etc information and will work as a master
CREATE TABLE Person (
[WorkTypeId] [int] IDENTITY(1,1) NOT NULL,
[WoryType] VarChar(250) NULL
)
CREATE Table Work (
[WorkId] [int] IDENTITY(1,1) NOT NULL,
[WorkType] INT NOT NULL,
[ListId] INT NOT NULL
)
create a table Person_X_WorkType
create a table Work_X_WorkType
Try to use the above two tables to fetch corresponding person for workType for a work, and once user select the person save it in the below table.
create a table Work_WorkType_Person
-
Core6430 (12/26/2010)
The database design is similiar to the tables below...What you are posting is not a database design per-se but a physical implementation of some database design.
Would you consider posting your data model here? like an ER Model? That will help us to help you.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
A design that contains a simple table to define what types of work a person/entity can do will be very easy to build and manage. Then use that to control the drop-downs and such in the application.
That's going to be easier to manage than having to re-write code every time someone negotiates a new contract and suddenly ABC Corp can no longer pick up from you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
PaulB-TheOneAndOnly (12/28/2010)
Core6430 (12/26/2010)
The database design is similiar to the tables below...What you are posting is not a database design per-se but a physical implementation of some database design.
Would you consider posting your data model here? like an ER Model? That will help us to help you.
This is what I was trying to describe above.
-
GSquared (12/28/2010)
A design that contains a simple table to define what types of work a person/entity can do will be very easy to build and manage. Then use that to control the drop-downs and such in the application.That's going to be easier to manage than having to re-write code every time someone negotiates a new contract and suddenly ABC Corp can no longer pick up from you.
That's what I was thinking too. Now how do I convince someone, who doesn't want to be convinced, that your design is more efficient? Last time I offered a suggestion he sent me links to sites that gave basic definitions for what "Tables" were. :crazy:
I really need to get this addressed soon, yet I'm stuck on the 'how to prove' there's a better way with irrefutable facts. Do you have any suggestions?
-
Core6430 (12/26/2010)
This is really troubling me, and I've been beating my head against a wall for 2 and a half weeks attempting to research/find logical arguments for or against this design. I feel it's going to cause trouble (big trouble) and I would like to prevent it from happening. Or I could be completely whacked out, and it's a fine design... However, I'm usually not wrong about this kind of stuff.I'll try to explain the goal in a general sense. I can't give actual details, or data, but the high level concept is products come in, based on the type and what we do to it, it is charged differently, then products go out.
All of the companies that we deal with, (ship to, bill to, pick up from, etc) are stored in one table (call it "Person" Table).
Each Person can have different classifications, can fill multiple roles in the business. For instance "ABC Corp" can be where we pick up supplies, and who we drop products off to. (Person's can also just be a regular Joe Schmoe Person, not a corporation. Like: average user purchased a USB stick).
We have a table that stores information about the work we performed. We'll call it "Work" table. Usually the path through the data starts at the Work table and needs information from the Person table (usually the name).
If we have a Work event that doesn't require picking up supplies from a company, then we don't want that company available for association to that Work event. This is where the "List" and "ListType" tables come in... If the Person isn't in the "List" of related Persons, then the user can't select them to associate it with a job. The idea was that the "ListListType" table say's if you're List Id 2, than you can choose Person records that match PersonType Ship to or Bill To, but not pick up from. I think this is a bad way to do this, and a bad place to put business logic like that too.
The database design is similiar to the tables below:
-- Holds every possible entity that we could sell to, ship to, interact with in anyway. This table also holds parent child relationships between Persons.
CREATE TABLE Person (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[PersonName] VarChar(250) NULL
)
INSERT INTO Person (PersonId, PersonName) VALUES (1111, 'ABC Corp')
INSERT INTO Person (PersonId, PersonName) VALUES (2222, 'DEF Corp')
CREATE Table Work (
[WorkId] [int] IDENTITY(1,1) NOT NULL,
[WorkType] INT NOT NULL,
[ListId] INT NOT NULL
)
INSERT INTO Work (WorkId, WorkType, ListId) VALUES (123, 5, 2131)
INSERT INTO Work (WorkId, WorkType, ListId) VALUES (124, 5, 2132)
CREATE Table List (
[ListId] [int] IDENTITY(1,1) NOT NULL,
[ListTypeId] VarChar(250) NULL
)
INSERT INTO List (ListId, ListTypeId) VALUES (2131, 1)
INSERT INTO List (ListId, ListTypeId) VALUES (2132, 2)
CREATE Table ListType (
[ListTypeId] [int] IDENTITY(1,1) NOT NULL,
[WorkTypeId] INT NOT NULL
)
INSERT INTO ListType (ListTypeId, WorkTypeId) VALUES (100, 5)
INSERT INTO ListType (ListTypeId, WorkTypeId) VALUES (101, 6)
CREATE Table ListListType (
[ListListTypeId] [int] IDENTITY(1,1) NOT NULL,
[ListTypeId] INT NOT NULL, -- Foreign key to ListType table
[PersonTypeId] INT NOT NULL -- Foreign key to Person Type table (last table)
)
INSERT INTO ListListType (ListListTypeId, ListTypeId, PersonTypeId) VALUES (1, 100, 998)
INSERT INTO ListListType (ListListTypeId, ListTypeId, PersonTypeId) VALUES (2, 101, 999)
CREATE Table ListPerson (
[ListPersonId] [int] IDENTITY(1,1) NOT NULL,
[ListId] INT NOT NULL,
[ListListTypeId] INT Not Null,
[PersonId] INT NOT NULL
)
INSERT INTO ListPerson (ListPersonId, ListId, ListListTypeId, PersonId) VALUES (300, 2131, 1, 1111)
INSERT INTO ListPerson (ListPersonId, ListId, ListListTypeId, PersonId) VALUES (301, 2131, 1, 2222)
-- this table has description of what the Person is. i.e. Bill to, Pick up from person, ship to person, etc.
CREATE Table PersonType (
[PersonTypeId] [int] IDENTITY(1,1) NOT NULL,
[Description] VarChar(250) NULL
)
INSERT INTO PersonType (PersonTypeId, Description) VALUES (998, 'Ship To Customer')
INSERT INTO PersonType (PersonTypeId, Description) VALUES (999, 'Pick Up From Customer')
My thought was to pull the tables that are attempting to regulate which "Work" records can connect to which "Person"s out of the database and put that in the application business layer instead. And even if we don't pull that logic out, then it seems like there's got to be a better, more logical, way to do this...
If you have some time, I would really appreciate any suggestions or thoughts you might have on this design. I really want to know the best way to do this (beyond getting a paycheck). It's one of those things that get in your head and you just can't let it go until you solve it, or at least understand it better. So, any help would be very appreciated.
If you even just have links to advanced db design, that would be helpful also. I've been reading up on everything I can find, and gathering all the information I can, to present a logical argument to them later, but I don't have a enough yet.
Thank you.
Pardon my bit of confusion but does that code represent the way it is or the way you think it should be? Either way, it represents only one side of the story. It may be helpful if you posted the other side of the story whatever it is. If you include the foreign keys, I can build my own ERD from it.
Also, you say...
and a bad place to put business logic like that too.
Why do you think that and what do you think foreign keys and the like actually do? If you didn't enforce such rules in the DB, where would you enforce them and how would you prevent casual users from making incorrect modifications/additions directly to the data in the DB?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That design is how it is.
Business logic for enforcing constraints is good. Buisness logic that changes frequently should be in code, not SQL.
I have no other side to the story. I've asked and asked. He gets confused explaining it. Instead, he made a new table so he wouldn't have to do the joins and manually updates it. There are no triggers, no jobs, no code to update the new table when new work is associated with a person.
This is why I think it's wrong.
-
Core6430 (12/29/2010)
Business logic for enforcing constraints is good. Buisness logic that changes frequently should be in code, not SQL.I'm sorry to break the news but this statement contradicts what the Book of Codd says - Rule #10: Integrity Independence 🙂
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Core6430 (12/28/2010)
PaulB-TheOneAndOnly (12/28/2010)
Core6430 (12/26/2010)
The database design is similiar to the tables below...What you are posting is not a database design per-se but a physical implementation of some database design.
Would you consider posting your data model here? like an ER Model? That will help us to help you.
This is what I was trying to describe above.
Thank you for posting the model.
mmhh... I'm having some issues understanding a couple of things - would you consider clarifying?
Let me just point the three first things that pop-up in front of my eyes:
1- Are all PKs subrogate keys? Any reason not to use Natural keys when available?
2- WORK and LIST tables
Does WORK.ListId points to LIST.Id?
Where is WORK.WorkTypeId pointing?
3- Relationshipt between WORK and PERSON
How do you manage to report which "persons" are assigned to a particular "work" - do you really have to join that many tables to get there?
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
PaulB-TheOneAndOnly (12/29/2010)
Thank you for posting the model.
mmhh... I'm having some issues understanding a couple of things - would you consider clarifying?
Let me just point the three first things that pop-up in front of my eyes:
1- Are all PKs subrogate keys? Any reason not to use Natural keys when available?
Yes, and i argued against having them all be surrogate keys. boss man refused to listen.
2- WORK and LIST tables
Does WORK.ListId points to LIST.Id?
Yes.
Where is WORK.WorkTypeId pointing?
That points to table that determines what kind of job it is. It would be similar to whether a company was purchasing USB drives for resale and then selling it. The concept is both of those activities would have a work number assigned to the act. So the work type would be like 'buy', 'sell'. That's over simplified but it's the overall concept.
3- Relationshipt between WORK and PERSON
How do you manage to report which "persons" are assigned to a particular "work" - do you really have to join that many tables to get there?
Yup. You got it.
Just to clarify... I've just stepped into this project recently. I wasn't there when they designed it or started developing it.
If you ran into a design like this, and had to get buy in to change it, how would you convince your partners/bosses of it? How would you approach it or what logic would you present to convince them?
Thank you for helping me with this.
-
The first point in getting buy-in on a redesign is finding out exactly how well it's working the way it is. If it fulfills the business needs, and the end users are happy with it, then don't change it just because of academic reasons. If, as is more likely, people hate or at least dislike it, if it has problems, if it's slow or if the data is getting lost/altered because of the design, then it'll be easy to say, "Hey, there are some standard design rules for databases that, if applied, will fix some of those problems."
But the initial discovery on what problems real users are having is the only place to start. And don't accept their manager's statements on it, if that's applicable. Get the honest opinions of the people "in the trenches" as it were.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Core6430 (12/29/2010)
Just to clarify... I've just stepped into this project recently. I wasn't there when they designed it or started developing it.
If you ran into a design like this, and had to get buy in to change it, how would you convince your partners/bosses of it? How would you approach it or what logic would you present to convince them?
Here is where Database Administration meets Project Management. In Project Management there is a single all-powerful character, "the project sponsor".
You have to figure it out who the "project sponsor" is then document your findings and run both the problems and the solutions with the project-sponsor.
If you get and AYE you go ahead and work on agreed changes, if you get a NAY forget about it and prepare yourself to support a poorly designed application.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply