Friends,
Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
AnilI'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.|||you're kidding, right?
you have four people, and one of them is a Database Designer, and you're asking whose job it is to design the database?
answer: not the DBA
the DBA's job is to create the physical database from the finished database design
answer: not the project leader
the project leader's job is to talk to management
answer: not the systems analyst
the system analyst's job is to figure out what the users really want, not what they're asking for
let's see, have i missed anybody?|||Yeah r937 you missed someone. You missed the poor suckers that have to perform all of those roles. I'm stuck in that position right now. I know the most about the business processes, db design, dba work, java coding, object modeling, reporting progress to management, and managing interaction with the end users. It sux but it acts as great leverage when negotiating salary.|||I don't see any reason or advantage to splitting the DBA and Database Designer roles. It sounds like a recipe for turf wars to me.
Anybody?|||Originally posted by blindman
I'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.
You took the words right out of my keyboard :)|||you can't see any advantage to splitting the DBA and Database Designer roles?
whoa, you must be trollin
where to start?
how about: if the Database Designer designs the database, sufficient time and effort will be devoted to the understanding and identification of candidate keys based on business logic, whereas if the DBA does it, you get surrogate autonumber primary keys on every table and $deity knows what other similar crap
how's that?
;)|||Do you mean to say a DBA has no knowledge of Database design ...
Buzz ... You are wrong ... what we are talking about is an Integration of DBA and DD roles ... and that would be absolutely great if the person posseses enough DD and DBA knowledge .|||Well, in large shops it doesn't seem to fly. I'D LOVE TO:
- do the analysis
- do the design
- and do what I do on a daily basis (DBA stuff)
Turf wars is what we have here.
But the sad part is, that after the design is handed over to me to implement (mind you I am not invited to any of the meatings held by the project development team), I find all kinds of design issues in about 70% of the projects. At that point it's too late to take it back to the designer, or rather to the systems analyst, because success is declared and propagated all the way to the top, and I still love my job ;) So what am I left with? MAKE IT WORK!!! Recently the "designers" started listening to me a little more (I AM SHOCKED!) and started implementing any data access through a stored procedure. So, having a break like this, I have a luxury to re-write the procedure if it needs to be, and even alter table structures and relations, because the data is not directly accessed. It's a workaround that I found, but it keeps me occupied (not right now, it's boring here, everything works...)|||no, i do not mean to say the DBA has no knowledge of database design
that'd be silly, and i wouldn't say that
and who said we were talking about an integration of roles?
i thought we were talking about a0060162742357's situation where there is already two people, one of them a database designer and the other a DBA, and the question is, whose job is it to design the database?
duh...|||Database Designer
DBA
Project Leader
Programmer
hmmm .. these guys are there and still the system analyst is designing the db ... wierd|||Unfortunately it really doesnt matter who does it because the pooch usually gets screwed right from the beginning and then you have to spend the rest of your days working around a flawed design.
those magnificent bastards|||Originally posted by Ruprect
those magnificent bastards Do they come any other way ?!?!
-PatP|||How about all the "little" projects where the project manager says something like: "We only have two tables to hold our data, why don't we just dump them in the products database?"
Hmm. Would the fact that it is not product data have anything to do with that decision (or lack thereof)?
Sadly, we have no database design group here. All the developers are free to come up with their own designs, which I find out about later. And let me tell you, we have a creative bunch around here...
How are all of your shops made up? We only have project managers, programmers, and DBA.|||All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.|||The word is "micromanagement"...Or is it 2 words?|||or the client who says i want to bring these 900 various access 97 2000 and excel files into a sql server database
bottom line the only one who cares that the model is done right, is you.
it's like paul newman said
"only cream and bastards rise"|||Originally posted by blindman
All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.
I kind of disagree about the database administration piece. It would be impossible for me to be a good DBA if I didn't know about database design. I can only do so much with backups, recoveries, replication, etc.
How would I optimize the indexes and do performance tuning well though if I didn't understand database design. Half of performance tuning is knowing how proper design works and seeing where you can improve performance by applying indexes properly in the design, restructuring entities to be more efficient, and identifying weak code through analysis of the underlying structure.|||I don't disagree with you. I just feel that tweaking indexes falls within the realm of the DBA (though a designer has to plan for indexes as well). Indexes can be changed without modifying the structure or functionality of the schema, so I don't think of them as being the exclusive realm of the designer.|||Friends,
Thanx for ur suggestions n explanations. So Can I conclude that:
A system analyst : feeds the DD with user reqs and other high level flow diagrams.
A DD : models and designs the DB
A DBA (with the help of DD) : creates the Physical Database and Admins it, gives it PL
A PL : uses the info from SA and DBA+DD to develope the System(software)
A programmer : An OX
Am I Right?
Regards,
Anil,
the inexperienced|||This is a typical distribution of tasks, but I would argue that it has several flaws.
I think the Database Designer should create the physical database, though in a development environment. When it is done (or during development) the DBA should review it, and the DBA should be responsible for rolling it out to the production environment.
Also, the Database Designer should be involved with the application design from the start, including requirements gathering and functionality. I think a lot of problems occur when a system analyst and other parties gather requirements and design interfaces and then just hand the package off the the designer with the instructions to "build this". An allegory would be many of Frank Lloyd Wright's architectural designs, which may have been innovative and attractive, but were structurally unsound. The Database Designer should be a critical part of the development team.|||any competent database designer who finds himself or herself not included in the overall systems design and user requirements analysis will not stay in that job for long
Showing posts with label designing. Show all posts
Showing posts with label designing. Show all posts
Wednesday, March 7, 2012
Database Designing tools
Hi all,
Does anyone know how I can design the database schema. I mean what tools can be used to the design the database and view the table relationships, etc. TIA.
Does anyone know how I can design the database schema. I mean what tools can be used to the design the database and view the table relationships, etc. TIA.
Vik!Enterprise Manager? Makes a pretty good job in SQL Server for this.
database design??
Hello,
I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.
2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..and
what sort of service or products they can supply. For instance a Employment agency maybe under professional services
Table 1 Customer
Customer_ID = primary key,,,, Sector_ID = Foreign key
Comapany Name, Address, Phone, Postcode etc
Tabel 2 Industry Sectors
Sector_ID = primary key,,,,Customer_ID= foreign key
banking, Education,Prof Services, etc
Table 3 Trading Activity
Trading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= Fk
Employment Agent, School, Lawyer etc
Table 4 Products
Products_ID = primary key,,,,Trading_ID = foreign key
Supply frozen foods, transport services, sports goods, etc
Table 5 Account
Account_ID = primary key,,,,Customer_ID = foreign key
Account Name, Credit Limit, Payment Terms, Open date, Account contact etc
One big point of confusion is, can I have the Customer_ID from the principal Customers table
in every table as a foreign key or must the tables be chained together one after the other as such.
Advice appreciated
Thanks
Hi
There are some problems in your design.First thers are some general rules you should keep in mind.
For 2 objects A B, if there are 1-many relation between A and B, primary key of A should be inculded as foreign key in B .
if there are many-1 relation between A and B, primary key of B should be inculded as foreign key in A .
if there are many-many relation between A and B, you need to create a new table A-B,primary key of B and A should be inculded in the new table
if there are 1-1 relation between A and B, Columns of A and B could be included in a single table .
For table1 and table2 if one customer belongs to many industry sectors and one industry sectors have many customers ,then you should create a new table with primary key of talbe1 and table2 inculded. Table 3 and talbe 4 are the same.
If I misunderstand your meaning ,pls tell me .
Database Design where some products are T-shirts with different sizes
Hello,
I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it.
What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts?
Should there be a column for each available size?
Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre"
The database is basically for a record company
If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram
Thanks
Here is my suggestion:
Table: Products
Id - PK
Name
Description
Price
Table: Attributes
Id - PK
Name
Label
Table: AttributeValues
Id - PK
AttributeId - FK
Value
Table: ProductAttributes
Id - PK
ProductId - FK
AttributeId - FK
In your order line, you will have to store the ProductID And then in a child table store the zero to many associated ProductAttributeId's. Here is an example
Table: Products
Id - 1
Name - Men's ABC Polo
Description - Nice Shirt
Price - 20.00
Id - 2
Name - Women's ABC Polo
Description - Nice Shirt
Price - 25.00
Table: Attributes
Id - 100
Name - Men's Sizes
Label - Size
Id - 101
Name - Women's Sizes
Label - Size
Table: AttributeValues
Id - 201
AttributeId - 101
Value - Small
Id - 202
AttributeId - 101
Value - Medium
Id - 203
AttributeId - 101
Value - Large
Id - 204
AttributeId - 101
Value - Extar Large
Id - 205
AttributeId - 102
Value - Extra Small
Id - 206
AttributeId - 102
Value - Small
Id - 207
AttributeId - 102
Value - Medium
Table: ProductAttributes
Id - 300
ProductId - 1
AttributeId - 101
Id - 301
ProductId - 2
AttributeId - 102
The attrubutes are reuseable accross multiple products and the use of attributes is optional. When you take the order you would store the product id of 1 for a Man's polo with the attribute value in another table relating to the line item. You put this in another table so you can have potentially multiple attribute values.
Make sense? Just one way of many... this was off the top of my head and it is late.
Database Design- Referencing multiple database
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.|||Hi All,
Hi.
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server.
Really...
Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
How do i design my database and sql queries?
In a word: "poorly".
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Well, that plan certainly is......"creative"...
Any thoughts on above approach?? or any better/standard way for such scenarios ?
I'd say, practically anything else would be better.
Thanks in Advance. Your inputs will be of great help.
No problem.|||Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server! ;)
On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.
I would implore you to think again.
Lempster|||That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?|||How versatile
but, um, how much experience do you have?
Oh, I get it, you're in upper manglement|||Ouch Brett...that'll likely leave a scar. ;)
I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!
Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?|||Hi All,
I am designing database
I didn't read that all
I designing a housebuilding business
I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)|||sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.|||how so?
too short|||i think this board has more of a tendency to slam the OP.|||Nothing like the real world, eh|||No soup for you, poster!|||There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.|||I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair. ;)
If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.
As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||What's wrong with just downloading the data from the other data sources via feeds - bcp each file into a transfer table - check the data - put the changed data into your database. How many feeds could you expect this way, what size (roughly) for each feed?
Your method may save you having to write any feeds but the people writing the app are going to hate you for the rest of their lives :)
Mike|||Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department.This sounds like a small data warehousing initiative to me. Perhaps that is the approach you should be taking.|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)|||Well the things getting more complex. We will be having four external database to depends on for our application.
Reason of staying away from distributed query was that our application requires this data (external database table serves as master table for our application) in the transactions. And of course in reports as well.
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.|||Originally posted by versatile_me
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.I'd like to claim first dibs on the concept but I suspect it's been around for 40 years.
I know it's fun comming up with new systems but after you spend 6 months sweating blood trying to get it to work there are only a few possible outcomes :
it fails and you have to call in a professional who is then faced with working with a flawed design or loosing the client by throwing everything away and starting afresh.
it works, but only just, and after 3 months limping along the system gets abandonded.
it works and is a fantastic success - I'm afraid this is the least likely outcome :)
If the application is at all important to your company then it may well be worth getting an experienced profesional in to do the work and you provide the business requirements. This would be a good way to learn about database/system design and also might ensure any good ideas you may have will appear in the finished product.
Mike|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)
...
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report.
Not so much "versitile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).|||I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.The difference is that in my system the data is not imported on a daily basis "as-is", but rather the existing tables are updated with NEW data that we did not previously have. However, I see your point in that the difference may be perception (and/or semantics) and my understanding of what the O.P. is contemplating as an implementation.
In my world, the data we import (via web service or via remote access) is not a "synching" of databases. It's the importation of data from external providers, and placed into our own "consolidated" and normalized database tables (and used for our own devious purposes completely unrelated to the original source(s) of the data). At least in my eyes, a completely different process than importing actual tables from a remote database and keeping them in synch.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report. I can't speak for this one, because we don't do it. We either provide access to our data via controlled (read-only) linked servers and associated userid's, or via a web service that is a front-end to our database.
We absolutely don't allow uncontrolled access to our tables, even remote access via linked servers/userid's is typically controlled with a view. If there is time and/or the longevity of the projects dictates, we write a web service wrapper to the external world and make them keep their nasty little grubbies off our databases.
We expect (and generally require) external/remote databases to have (or support) the same indirect access. We don't want our applications failing because they changed a table on the external system.
Not so much "versatile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).ed-zachary :)|||i think this board has more of a tendency to slam the OP.
Really?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782&whichpage=8|||ROTFLMAO ... like Mastercard ... that was priceless :D
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.|||Hi All,
Hi.
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server.
Really...
Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
How do i design my database and sql queries?
In a word: "poorly".
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Well, that plan certainly is......"creative"...
Any thoughts on above approach?? or any better/standard way for such scenarios ?
I'd say, practically anything else would be better.
Thanks in Advance. Your inputs will be of great help.
No problem.|||Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server! ;)
On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.
I would implore you to think again.
Lempster|||That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?|||How versatile
but, um, how much experience do you have?
Oh, I get it, you're in upper manglement|||Ouch Brett...that'll likely leave a scar. ;)
I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!
Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?|||Hi All,
I am designing database
I didn't read that all
I designing a housebuilding business
I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)|||sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.|||how so?
too short|||i think this board has more of a tendency to slam the OP.|||Nothing like the real world, eh|||No soup for you, poster!|||There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.|||I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair. ;)
If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.
As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||What's wrong with just downloading the data from the other data sources via feeds - bcp each file into a transfer table - check the data - put the changed data into your database. How many feeds could you expect this way, what size (roughly) for each feed?
Your method may save you having to write any feeds but the people writing the app are going to hate you for the rest of their lives :)
Mike|||Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department.This sounds like a small data warehousing initiative to me. Perhaps that is the approach you should be taking.|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)|||Well the things getting more complex. We will be having four external database to depends on for our application.
Reason of staying away from distributed query was that our application requires this data (external database table serves as master table for our application) in the transactions. And of course in reports as well.
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.|||Originally posted by versatile_me
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.I'd like to claim first dibs on the concept but I suspect it's been around for 40 years.
I know it's fun comming up with new systems but after you spend 6 months sweating blood trying to get it to work there are only a few possible outcomes :
it fails and you have to call in a professional who is then faced with working with a flawed design or loosing the client by throwing everything away and starting afresh.
it works, but only just, and after 3 months limping along the system gets abandonded.
it works and is a fantastic success - I'm afraid this is the least likely outcome :)
If the application is at all important to your company then it may well be worth getting an experienced profesional in to do the work and you provide the business requirements. This would be a good way to learn about database/system design and also might ensure any good ideas you may have will appear in the finished product.
Mike|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)
...
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report.
Not so much "versitile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).|||I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.The difference is that in my system the data is not imported on a daily basis "as-is", but rather the existing tables are updated with NEW data that we did not previously have. However, I see your point in that the difference may be perception (and/or semantics) and my understanding of what the O.P. is contemplating as an implementation.
In my world, the data we import (via web service or via remote access) is not a "synching" of databases. It's the importation of data from external providers, and placed into our own "consolidated" and normalized database tables (and used for our own devious purposes completely unrelated to the original source(s) of the data). At least in my eyes, a completely different process than importing actual tables from a remote database and keeping them in synch.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report. I can't speak for this one, because we don't do it. We either provide access to our data via controlled (read-only) linked servers and associated userid's, or via a web service that is a front-end to our database.
We absolutely don't allow uncontrolled access to our tables, even remote access via linked servers/userid's is typically controlled with a view. If there is time and/or the longevity of the projects dictates, we write a web service wrapper to the external world and make them keep their nasty little grubbies off our databases.
We expect (and generally require) external/remote databases to have (or support) the same indirect access. We don't want our applications failing because they changed a table on the external system.
Not so much "versatile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).ed-zachary :)|||i think this board has more of a tendency to slam the OP.
Really?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782&whichpage=8|||ROTFLMAO ... like Mastercard ... that was priceless :D
Database design question with time
Im designing a database where a user enters the date and the number of
hours and minutes he worked for the day..now i can do this
hours and minutes he worked for the day..now i can do this
Workdate small date
Hours integer
Minutes integer
But then would I have to have the front end know that when adding up
the hours and minutes for the week that 60 minutes = 1 hour or is
there some way to do this in the database?
thanks
-JimJim (jim.ferris@.motorola.com) writes:
> Im designing a database where a user enters the date and the number of
> hours and minutes he worked for the day..now i can do this
> Workdate small date
> Hours integer
> Minutes integer
>
> But then would I have to have the front end know that when adding up
> the hours and minutes for the week that 60 minutes = 1 hour or is
> there some way to do this in the database?
You could have a computed column with the forumla Minutes + 60 * Hours:
CREATE TABLE workhours (
userid userid_type NOT NULL,
workdate smalldatetime NOT NULL,
hours tinyint NOT NULL,
minutes tinyint NOT NULL,
worked_minutes AS minutes + 60 * hours,
CONSTRAINT pk_workhours(userid, workdate))
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> ... database where a user enters the date and the number of hours
and minutes he worked for the day..<<
Use a duration instead of trying to create your own temporal datatype
system.
CREATE TABLE Timecard
(emp_id INTEGER NOT NULL,
start_time DATETIME NOT NULL,
finish_time DATETIME, -- null means still active
PRIMARY KEY (emp_id, start_time));
You can now use BETWEEN predicates and a calendar table.|||Thanks Ill try that
-Jim
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9474F24297CBCYazorman@.127.0.0.1>...
> Jim (jim.ferris@.motorola.com) writes:
> > Im designing a database where a user enters the date and the number of
> > hours and minutes he worked for the day..now i can do this
> > Workdate small date
> > Hours integer
> > Minutes integer
> > But then would I have to have the front end know that when adding up
> > the hours and minutes for the week that 60 minutes = 1 hour or is
> > there some way to do this in the database?
> You could have a computed column with the forumla Minutes + 60 * Hours:
> CREATE TABLE workhours (
> userid userid_type NOT NULL,
> workdate smalldatetime NOT NULL,
> hours tinyint NOT NULL,
> minutes tinyint NOT NULL,
> worked_minutes AS minutes + 60 * hours,
> CONSTRAINT pk_workhours(userid, workdate))
Saturday, February 25, 2012
Database Design Question
I am right now in process of designing a database for hosting business. Now
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
Niel
Niel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegr oups.com...[vbcol=seagreen]
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
Niel
Niel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegr oups.com...[vbcol=seagreen]
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Database Design Question
I am right now in process of designing a database for hosting business. Now
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
NielNiel wrote:
> I am right now in process of designing a database for hosting business. No
w
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the packa
ge
> wherein they might want to add one of two additional feature that are not
a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly shoul
d
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column tha
t
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype
)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional featur
e
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also i
n
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to thi
s
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegroups.com...
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this[vbcol=seagreen]
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
NielNiel wrote:
> I am right now in process of designing a database for hosting business. No
w
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the packa
ge
> wherein they might want to add one of two additional feature that are not
a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly shoul
d
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column tha
t
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype
)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional featur
e
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also i
n
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to thi
s
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegroups.com...
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this[vbcol=seagreen]
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Database design question
I'm in the process of designing a database and would like some
suggestion on how to architect it.
suggestion on how to architect it.
I have 3 'objects', frequency, task, skill.
For each task there is a frequency however for some tasks there are a
number of different skills, each of which have different frequencies,
so, for example, imagine frequencies being time in minutes. Each task
has to be done a certain number of times but certain tasks have varied
skill levels and each skill level has different frequencies associated
with it.
Task 1, Freq 5min
Task 2, Freq 10min
Task 3, Skill 1, Freq 5min
Task 3, Skill 2, Freq 15min
Task 4, Skill 1, Freq 10min
Task 4, Skill 2, Freq 15min
Task 4, Skill 3, Freq 20min
The problems is that the freq / skill is dependent on the task. I'm
having trouble deciding how to build this database.
I could just hard code the Tasks/Skill in a table so that I have
TaskID PK
Task
Freq
and have records like:
1 Task1 5
2 Task2 10
3 Task3Skill1 5
4 Task3Skill2 15
but I don't like it because it is not easily maintained - in case
freq/skills change.
Does anyone have any ideas. The important information is the
frequency as it will be used to build timetables.>> I have 3 'objects', frequency, task, skill. <<
Basic question - in your business model are these separate entities or
attributes of an entity? What are the dependencies among them? The answer to
these questions lay the foundation for the table design.
>> I could just hard code the Tasks/Skill in a table ... <<
That violates 1NF (assuming tasks & skills are different attributes) and
based on my interpretation of your requirements would cause an update/delete
anomaly. Generally database design cannot be accomplished using Newsgroup
responses since it requires a comprehensive understanding of your underlying
business model, rules & requirements. However, based on a series of
assumptions, here is one way of SQL representation :
CREATE TABLE Tasks (
Task_id INT NOT NULL PRIMARY KEY,
Details VARCHAR(30) NOT NULL,
...);
CREATE TABLE Skills (
Skill_id INT NOT NULL PRIMARY KEY,
Decription VARCHAR(40) NOT NULL,
...);
CREATE TABLE TaskSkills (
Task_id INT NOT NULL
REFERENCES Tasks(Task_id),
Skill_id INT NOT NULL
REFERENCES Skills(Skill_id),
Freq INT NOT NULL
PRIMARY KEY(Task_id, Skill_id));
--
- Anith
( Please reply to newsgroups only )
database design question
Hi!
We are designing a big database with multiple parts (like Human Resources, Sales, Production) etc. in Sql server 2005. Should we devide the main parts into separate databases, or should we put all tables in a single database and use schemas to group the parts (like the AdventureWorks sample database?). Is there a general recommendation here?
We are considering multiple databases so one more easily could move one database to a diffrent server if needed.
Hi,
I would suggest you put all the tables in a database and devide them with schemas, just as AdventureWroks shows. Putting the tables across will result in many problems, and performance hits.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||If we devide into multiple databases, we will try to minimize the references between them. After all they represent different services within the enterprise solution.
I realize that the administrative tasks increase as the database count increase, but why would it be related with performance hits? If the traffic to one of the databases becomes an issue, we can easily move it to a new fresh server and thus gain performance.
Could you also be more specific on what other problems you refere to.
Thanks!
Database design question
I’m relatively new at database design so forgive me if I’ve overlooked
something stupid.
I’m designing a teacher grading application on the new SQL 2005 Express.
Right now I’ve got a Student table that lists info on each student. I the
n
have a Grades table that lists grades for each assignment that is linked to
an assignment table. The grades table and the student table are linked with
a relationship. I’d like to create a view that lists the student name wit
h
each corresponding grade for each assignment in a new column. When do a
simple select statement I get a row for each grade, I just want one row for
each student with a assignment grade in separate columns. Is this possible?It can be done on the server (in T-SQL, even) but it *really should* be done
in the presentation layer.
Read more here:
http://www.windowsitpro.com/Article...5608/15608.html
Data seems more user-friendly using this technique, however, an important
informational value is lost this way - the relationship. So, I'd suggest
using this technique for presentation purposes only, better yet - let the
presentation engine take care of it.
ML
something stupid.
I’m designing a teacher grading application on the new SQL 2005 Express.
Right now I’ve got a Student table that lists info on each student. I the
n
have a Grades table that lists grades for each assignment that is linked to
an assignment table. The grades table and the student table are linked with
a relationship. I’d like to create a view that lists the student name wit
h
each corresponding grade for each assignment in a new column. When do a
simple select statement I get a row for each grade, I just want one row for
each student with a assignment grade in separate columns. Is this possible?It can be done on the server (in T-SQL, even) but it *really should* be done
in the presentation layer.
Read more here:
http://www.windowsitpro.com/Article...5608/15608.html
Data seems more user-friendly using this technique, however, an important
informational value is lost this way - the relationship. So, I'd suggest
using this technique for presentation purposes only, better yet - let the
presentation engine take care of it.
ML
Database Design Question
I am right now in process of designing a database for hosting business. Now
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
NielNiel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegroups.com...
> Niel wrote:
> > I am right now in process of designing a database for hosting business.
Now
> > just like other hosting companies even this hosting companies has its
> > different web hosting packages. But besides that the company is going to
> > provide a feature to customer where in they can select/customize the
package
> > wherein they might want to add one of two additional feature that are
not a
> > part of the standard package.
> > I have designed most of that tables but i am confused on how exactly
should
> > i design the "User Defined Package" table.
> > There are one of the two things that i can do.
> >
> > 1) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have a "featureid" (integer datatype) column
that
> > would be referencing "Features" table.
> > The problem here is that it would be easily be managable from
programming
> > aspect but there wil be redundancy factor since if a same customer takes
5
> > additional features then there would be 5 rows with same customer id and
> > separate featureid and this is just for one customer. If there is a
large
> > customer base it could create space issues as well.
> >
> > 2) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have another column featureids (varchar
datatype)
> > that would have all the additional feature ids seperated by a
deliminator.
> > There won't be redundancy in this case but would make things little bit
> > complicated from programming aspect since everything new additional
feature
> > to to be added or edited or to be removed will require some work in the
> > code.
> >
> > Which method should i go for that would be helpful not just now but also
in
> > future as the customer base increases.
> >
> > I do not have anything else in mind. If there is any other solution to
this
> > all the suggestions are welcomed.
> >
> > Thank you
> > Niel
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
NielNiel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegroups.com...
> Niel wrote:
> > I am right now in process of designing a database for hosting business.
Now
> > just like other hosting companies even this hosting companies has its
> > different web hosting packages. But besides that the company is going to
> > provide a feature to customer where in they can select/customize the
package
> > wherein they might want to add one of two additional feature that are
not a
> > part of the standard package.
> > I have designed most of that tables but i am confused on how exactly
should
> > i design the "User Defined Package" table.
> > There are one of the two things that i can do.
> >
> > 1) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have a "featureid" (integer datatype) column
that
> > would be referencing "Features" table.
> > The problem here is that it would be easily be managable from
programming
> > aspect but there wil be redundancy factor since if a same customer takes
5
> > additional features then there would be 5 rows with same customer id and
> > separate featureid and this is just for one customer. If there is a
large
> > customer base it could create space issues as well.
> >
> > 2) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have another column featureids (varchar
datatype)
> > that would have all the additional feature ids seperated by a
deliminator.
> > There won't be redundancy in this case but would make things little bit
> > complicated from programming aspect since everything new additional
feature
> > to to be added or edited or to be removed will require some work in the
> > code.
> >
> > Which method should i go for that would be helpful not just now but also
in
> > future as the customer base increases.
> >
> > I do not have anything else in mind. If there is any other solution to
this
> > all the suggestions are welcomed.
> >
> > Thank you
> > Niel
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Friday, February 24, 2012
Database design issue
hi,
we are designing a web application for defect tracking. In this for each
project the user can choose his own template for writing testcases... we nee
d
to persist this data.As you can see there can be any number of textbox in
that template.
We suggested two models,
1. a table with a text column holding the XML format which contains the
template with data .
2. a table containing variable name and value as column such that a crosstab
query would return the template values for a particular project.
But the .net developers suggest creating the table on the fly while the
template is chosen.. Please note once a template is chosen for the project i
t
remains the same.
which one would be better?If you are not going to query within the template and get a subset of
testcases.you can use the XML method.But we don't have validation techniques
for the XML schema in SQL 2000. So if that's not a problem, then storing as
XML should be the best option.
But if you have cases like, the user will select testcase 5 in the template
and edit it and save it, then you are better off with the second option
because manipulation of XML is tough.
In case of option 1, You can take out the XML and edit it and replace the
old one with the new one. If thats fine, then go for option 1.
Hope this helps.
we are designing a web application for defect tracking. In this for each
project the user can choose his own template for writing testcases... we nee
d
to persist this data.As you can see there can be any number of textbox in
that template.
We suggested two models,
1. a table with a text column holding the XML format which contains the
template with data .
2. a table containing variable name and value as column such that a crosstab
query would return the template values for a particular project.
But the .net developers suggest creating the table on the fly while the
template is chosen.. Please note once a template is chosen for the project i
t
remains the same.
which one would be better?If you are not going to query within the template and get a subset of
testcases.you can use the XML method.But we don't have validation techniques
for the XML schema in SQL 2000. So if that's not a problem, then storing as
XML should be the best option.
But if you have cases like, the user will select testcase 5 in the template
and edit it and save it, then you are better off with the second option
because manipulation of XML is tough.
In case of option 1, You can take out the XML and edit it and replace the
old one with the new one. If thats fine, then go for option 1.
Hope this helps.
Database design in SQL Server 2005
Hi
I am facing a problem in designing a database for my project.Please help
I have hotel Information.The hotel allocates rooms for my company.This is done on weekly basis.
Now suppose in first week of a year the number of rooms allocated to the company is 3,2ndweek =5,3rdWeek=5 and so on...
So when i search based on a week i should have one result set
If i search based on MOnth i should have one result set.. in this way.
So what fields i need to take in a database table so that when i search based on week /month/quarter/year i get different resultsets.
Create a table called Room with all room details and a roomId, booked date etc. Create a Search stored procedure which take searchType as an input parameter. Search type can be Weekly or Monthly. SP would be like:
if @.searchType = "Weekly"
//get the start date, calculate end date of the week (which simply could be startDate +7 ) and select the rooms booked within this date
if @.searchType = "Monthly"
//get the start date and calcuate the end date of the month and select rooms booked in this date range
So basically you need just a start date, and depending on the comparison type, you calculate the end date and search within this date range
Hope this helps,
Vivek
|||
Hotels(Pid | Hotel_Name | Address)
Rooms_Type(like master, single, deluxe etc)
Pid | Description
Hotel_Rooms (Pid | Hotel_ID | Room_ID )
Room_Allocations (Pid | Date | BookedYN | Booking_No | Hotel_Rooms_ID | Qty_Allotted )
above is the list of tables i think should be helpful to you. Room_Allocations is key table here that can help you out. Note : say you've Hotel Crown Plaza, for which the quantity allotted to you for Master Room is 5, in that case you will insert 5 records in Room_Allocation table. now when you book a room you'll insert booking_reference against a room where BookedYN is 'N'.
hope it makes sense.
regards,
satish
|||
Thanks Vivek !!!
Your idea has helped me.
Hey Satish
Thanks for sharing your ideas...
|||
no worries.
cheers,
satish.
|||HI
Thanks for your help .I have a doubt.
Plz help
i have a tble structure like this:
Id St_dt end_dt hot_id no_of_room
1 Jan1 Jan30 1 5
Now i want to enter another data like
2 Jan15 Jan20 1 7
what i want is my actual table should have 3 rows of data like
Id St_dt end_dt hot_id no_of_room
1 Jan1 Jan15 1 5
2 Jan15 Jan20 1 7
3 Jan20 Jan30 1 5
How do i do this. Or is there any other way of avoiding data overlapping
Database Design Help !
Hi !!
We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:
1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)
2) We have a Tables called : Interest & Customer_Interest
Customer Table:
CustomerID, Customer Name, Address, Email, Signup Date
Interest Table:
InterestID, InterestName
Customer_Interest: (Need suggestion for How to design this)
Should Table be design like:
Option1: CustomerID, InterestID
Option2: CustomerID, Interest1, Interest2, Interest3, Interest4
i.e.
Lets Say:
Customer table has CustomerA, CustomerB, CustomerC
Interest table has Interest I1, I2, I3, I4
Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4
As per Option1:
Customer_Interest Table witll have
CustomerA, I1
CustomerA, I2
CustomerA, I3
CustomerB, I1
CustomerB, I4
OR
As per Option2
Customer_Interest (Where Interest Column is bit column.... 1 = Signed up, 0 = Not Signed up
CustomerA, 1, 1, 1, 0
CustomerB, 1, 0, 0, 1
Which way we should design?
3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?
Thanks !!!You should go with Option 1. It would scale as the interest table changes. Option 2 would be a maintenance nightmare to change as the Interest table changes - not to mention, you would have to interpret the boolean values.|||1) What are the business requirements for identifying a customer? Does the business provide a Customer Number? If the requirements provide a unique identifier, then use what the business provides.
2) As the other poster said, your second option wouldbe a total nightmare. If you really wanted data presented in that view, then you can easily create a view to do that from your normalized table.|||
1) Here is the article which gave indicated Option 2:
http://www.devx.com/dotnet/Article/20040/0/page/1
2) I liked your suggestion about Starting Identity with 10000 to get consistant length
3) If we use Web Services for Data Access and Business Layer, is it good / bad?
4) How can we apply some software design patterns ? I am looking into MVC but reading few things on web tells me that with .NET 2.0 it has got some issues. Most of our recent development is in .NET 2.0 ? Any ideas?|||
There is not anything intrisically wrong with using an identity or guid column, and in fact doing so can provide many benefits. If I were you, I'd research the topic more before taking a scapel to all of those identity keys.|||
Thanks for your suggestions.
We are looking into it. I don't know if you looked into the revised schema I posted or not.
[AddressBook]:
- We will rename it
- We can't use CustID + AddressType as PK. Since this is a shopping, person can have more than one Shipping or Billing Address. (Something similar to Amazon or BN who allows you to store multiple shipping and billing address)
[Categories]:
- Client Defined. We removed Identity Column and used Category Name as PK
[Customers]:
- Made change as per your suggestion
[DeliveryZip]
- Looking into it
[Log]:
- Error Logs.
[Orders]:
- Will have to think if we can do that or go with Identity starting with 100000 or something.
[Payments]:
- I think we don't need to rename it. Payment actually stores Credit Card info and amount charged on their Credit card. We never show customer their Credit Card info upon their next visit. Payment ID is referenced in Orders Table to know how customer has paid and how much was charged to him including everything.
Correct me if I am wrong.
[Products]:
- Client doesn't have SKU for products. We may go with Identity column starting with 1000 or something.
[ShoppingCart]:
- CartID is not sequential. It is random.
- CartID can't be PK coz, if I add more than 1 item into my cart, CartID is going to get repeated.
E.g.
CartID ProductID Qty
AAAA 1000 2
AAAA 2012 1
-- We have used IBuySpy Portal as our reference and some of the design is based on that. This indicates to us that we can't depend on design like that. Correct?
We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:
1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)
2) We have a Tables called : Interest & Customer_Interest
Customer Table:
CustomerID, Customer Name, Address, Email, Signup Date
Interest Table:
InterestID, InterestName
Customer_Interest: (Need suggestion for How to design this)
Should Table be design like:
Option1: CustomerID, InterestID
Option2: CustomerID, Interest1, Interest2, Interest3, Interest4
i.e.
Lets Say:
Customer table has CustomerA, CustomerB, CustomerC
Interest table has Interest I1, I2, I3, I4
Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4
As per Option1:
Customer_Interest Table witll have
CustomerA, I1
CustomerA, I2
CustomerA, I3
CustomerB, I1
CustomerB, I4
OR
As per Option2
Customer_Interest (Where Interest Column is bit column.... 1 = Signed up, 0 = Not Signed up
CustomerA, 1, 1, 1, 0
CustomerB, 1, 0, 0, 1
Which way we should design?
3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?
Thanks !!!You should go with Option 1. It would scale as the interest table changes. Option 2 would be a maintenance nightmare to change as the Interest table changes - not to mention, you would have to interpret the boolean values.|||1) What are the business requirements for identifying a customer? Does the business provide a Customer Number? If the requirements provide a unique identifier, then use what the business provides.
2) As the other poster said, your second option wouldbe a total nightmare. If you really wanted data presented in that view, then you can easily create a view to do that from your normalized table.|||
1) We are going to assign CustomerID. Usually we use Identity Column as CustomerID. After reading these articles, SQL server Forum, this forum etc etc, we started thinking if we should use Identity column as CustomerID or in our system people has to login.. so Can we use Email as Primary Key and use Identity Column as Auto number column or Not to use at all.
2)
I got idea for option 1 and option 2 from reading an article. Interest are going to Change. System will have set of predefined Interest.
Lets say if I go with Option 1:
As a user I selected Interest I1, I2
After sometime (few days) I update my profile. I unregister for Interest I1 but signup for Interest I3.. so now I have Interest I2, I3
In this case, should I remove the row from database for I1, and then add new row to database I3?
1) I've found that IDENTITY are the easiest to use for situations like this. I'd recommend starting them at 10000 to get a consistant length.
2) I'm curious -- I'd be interested in seeing the article you found a suggestion for Option2 in.
The idea is that you have a UserInterest table that contains each users interest -- so every time your interests change, you insert/delete from that table.
If you want a flat view of users with specific interests, just a matter of one join per interest ...
SELECT Username,
CASE WHEN I1.InterestId IS NULL THEN 0 ELSE 1 as Interest1,
CASE WHEN I2.InterestId IS NULL THEN 0 ELSE 1 as Interest2,
CASE WHEN Ix.InterestId IS NULL THEN 0 ELSE 1 as InterestX,
FROM Users
JOIN UserInterests I1 ON Users.UserId = I1.UserId
JOIN UserInterests I2 ON Users.UserId = I2.UserId
JOIN UserInterests IX ON Users.UserId = IX.UserId
1) Here is the article which gave indicated Option 2:
http://www.devx.com/dotnet/Article/20040/0/page/1
2) I liked your suggestion about Starting Identity with 10000 to get consistant length
3) If we use Web Services for Data Access and Business Layer, is it good / bad?
4) How can we apply some software design patterns ? I am looking into MVC but reading few things on web tells me that with .NET 2.0 it has got some issues. Most of our recent development is in .NET 2.0 ? Any ideas?|||
Another Database Design Issue:
We are designing a Shopping Cart forConfectionery Items. For Some Items Customer can select toppings and each toppings cost 0.50$.
Here is Our Sample Table
Products ( 0 = No and 1 = Yes )
ProductID ProductName ProductPrice CanShip CanDeliver
P1 Product 1 25 0 1
P2 product 2 50 1 1
How do we make handle Toppings ?
Lets Say there are 5 toppings options available. For Product 2, customer can choose toppings. How do we handle this in database design?
You'll need a Toppings table, and a table to represnt the one-to-many relationship that you are looking to model.
You will also, of course, need an Orders table. And I would be careful about the way you have the ProductPrice listerd; you might have issues if you decide to change the price...
|||I have read articles
1)http://www.sqlteam.com/item.asp?ItemID=2599,
2)http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=3804&FORUM_ID=5&CAT_ID=3&Topic_Title=Creating+a+table+of+information+based+from+other+t&Forum_Title=Developer
As per them using Identity Column as Primary Key is not good idea.
Now in our design we have used Identity Column for every table as Primary Key.
Let me write down table
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppDomainName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessID] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThreadName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32ThreadId] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormattedMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NULL ,
[ModelNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductImage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDeliver] [bit] NOT NULL ,
[CanPickUP] [bit] NOT NULL ,
[CanShip] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Toppings] (
[ToppingID] [int] NOT NULL ,
[ToppingName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
|||
Arbitrarly putting an IDENTITY column on every table is what many of us refer to as an "ID-iot" design. Generally, IDENTITY makes a poor choice as a primary key because it is not predicible or verifiable. But that's a whole other discussion and is something that is very likely out of the scope of your project.
I will comment on your schema though ...
Your naming convention is poor. Data elements should follow ISO-11179 standards. Some examples:
- CanShip should be Shippable_Indicator
- DateCreated should be Creation_Date
- ShoppingCart should be ShoppingCarts
- DeliveryZip should be DeliveryRates
I see no constraints what so ever defined. do you want people to order -6 of an item?
You should avoid the MONEY type. Use DECIMAL instead.
Your model could use some improvement, especially with keys. Eg,
- RecordID is pointless
- An address should be (Cust_Id + Addres_Type)
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details
Alex... constraints are defined. However when I generated SQL script I forgot to check that in SQL Server. I will post script again tomorrow once I am at office.
1) Looking at above schema, can you suggest where can we remove Identity Column as Primary Key
2) I agree, we never followed any naming convention standard. Thanks for pointing out. Where can I see ISO-11179 standards? We will work on it.
3) Thanks for pointing Money / Decimal suggestion. Can you tell me why Money should be avoided?
4)
- I got your suggestion for RecordID.
- An address should be (Cust_Id + Addres_Type) -- What does it mean ?
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details. -- How do I calculate OrderTotal along with Shipping and Tax from Order_Details
There is not anything intrisically wrong with using an identity or guid column, and in fact doing so can provide many benefits. If I were you, I'd research the topic more before taking a scapel to all of those identity keys.|||
Thanks pjmcb for looking into it. Now if I see comments from you and Alex, its contradictory. I will look into suggestions Alex made to me.
In the mean while here is the Schema with Constraints as I promised...
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[MI] [varchar] (2) NULL ,
[LastName] [varchar] (50) NOT NULL ,
[Address1] [varchar] (100) NOT NULL ,
[Address2] [varchar] (100) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [varchar] (2) NOT NULL ,
[Zip] [varchar] (10) NOT NULL ,
[Phone] [varchar] (50) NOT NULL ,
[AddressType] [varchar] (20) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) NOT NULL ,
[Location] [varchar] (20) NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) NOT NULL ,
[Title] [nvarchar] (256) NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) NOT NULL ,
[AppDomainName] [nvarchar] (2048) NOT NULL ,
[ProcessID] [nvarchar] (256) NOT NULL ,
[ProcessName] [nvarchar] (2048) NOT NULL ,
[ThreadName] [nvarchar] (2048) NULL ,
[Win32ThreadId] [nvarchar] (128) NULL ,
[Message] [nvarchar] (2048) NULL ,
[FormattedMessage] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (10000, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) NOT NULL ,
[CreditCardNo] [varchar] (50) NOT NULL ,
[ExpMonth] [varchar] (50) NOT NULL ,
[ExpYear] [varchar] (50) NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1000, 1) NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[ModelNumber] [varchar] (50) NULL ,
[ModelName] [varchar] (50) NOT NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (4000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SubCategories] (
[SubCategoryID] [int] IDENTITY (100, 1) NOT NULL ,
[SubCategoryName] [varchar] (50) NOT NULL ,
[CategoryName] [varchar] (50) NOT NULL ,
[CanShip] [bit] NULL ,
[CanDeliver] [bit] NULL ,
[CanPickup] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Toppings] (
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [DF_AddressBook_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_AddressBook] PRIMARY KEY CLUSTERED
(
[AddressBookID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Categories] ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [DF_Customers_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED
(
[CustomerID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Log] ADD
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Orders] UNIQUE NONCLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [PK_BillingInfo] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [DF_Products_ModelNumber] DEFAULT ('') FOR [ModelNumber],
CONSTRAINT [DF_Products_Description] DEFAULT ('') FOR [Description],
CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [PK_ShoppingCart] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [PK_SubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Toppings] ADD
CONSTRAINT [PK_Toppings] PRIMARY KEY CLUSTERED
(
[ToppingName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [FK_AddressBook_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
)
GO
ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO
ALTER TABLE [dbo].[Order_Toppings] ADD
CONSTRAINT [FK_Order_Toppings_OrderDetails] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[OrderDetails] (
[ItemID]
),
CONSTRAINT [FK_Order_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Payments] FOREIGN KEY
(
[PaymentID]
) REFERENCES [dbo].[Payments] (
[PaymentID]
)
GO
ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [FK_Payments_AddressBook] FOREIGN KEY
(
[AddressBookID]
) REFERENCES [dbo].[AddressBook] (
[AddressBookID]
)
GO
ALTER TABLE [dbo].[Prod_Toppings] ADD
CONSTRAINT [FK_Prod_Toppings_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
),
CONSTRAINT [FK_Prod_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [FK_Products_SubCategories] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[SubCategories] (
[SubCategoryID]
)
GO
ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO
ALTER TABLE [dbo].[ShoppingCart_Toppings] ADD
CONSTRAINT [FK_ShoppingCart_Toppings_ShoppingCart] FOREIGN KEY
(
[RecordID]
) REFERENCES [dbo].[ShoppingCart] (
[RecordID]
)
GO
ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [FK_SubCategories_Categories] FOREIGN KEY
(
[CategoryName]
) REFERENCES [dbo].[Categories] (
[CategoryName]
)
GO
|||
Good to hear you have constriants -- a lot of folks don't use them.
ISO-11179 has now officially been opened up for free. The wikipedia has a very basic article on it, so go search in there for it. I hear it has links to download the standards. The most useful part you will find is part-5. That contains lots of info on how to name your data elements.
The money type is completely worthless and an overall pain to work with. It's propietary and you always need to include a currency symbol infront of it. Makes selecting and querying for it a complete pain in the butt. If your products will be in different currencies, you should split that into two columns (Cost_Amt, Cost_Cur) and use ISO 4217 codes for currency. If you ever need to convert currencies, you can have a simple EXCHANGE_RATES table and do a quick join. Try doing that with MONEY type.
I've looked at your tables, here's where you can improve your keys and other things ...
[AddressBook] - I would name this Addresses or CustomerAddresses. Consider that a customer can have only one address of each type (which I'm assuming is user-defined, like "Home" or "Work"). Therefore, the PK should be (CustId, AddressType).
[Categories] - Who defines these and how will they be used? Generally I would say have CategoryName be the PK, but if it's part of a URL, that can get ugly. I'm assuming that they will be part of the URL, so I would recommend making a Category_Code, 3 or 5 digits (don't know how many or what categories there are), and codifying the categories.
[Customers] - This looks fine although who wants to be Customer #12? seed your identity at 10000, 100000 or whatever you think is one greater than the magnitude of customers (1000's of customers, use 10000, etc).
[DeliveryZip] - I would name this DeliveryRates. ZIP Codes are 5 digits, and only 5 digits (CHAR(5)). ZIP+4 consist of two parts: 5 and 4 digits. Pick which one you want to use, and use it consistantly. Also, I have no idea what Location is. Is this a Longitude/Lattitude? A city name? If you want to use this is a city lookup, that's fine, but include a state. Then you can auto-populate the City/State when customer enters ZIP code.
[Log] - I have no idea how your using log table, generally these are used to just dump logging/tracint data into. I would recommend having your log table be *off* primary table space and contain no primary key. It's just a conviennt way to have a flat file.
[Orders] - I would strongly recommend coming up with a keying scheme for your orders. This will make things monumentally easier for users of the system. It depends on how many orders you will be getting, and that sort of thing, but here is one I use
Y-DDD-SSSS (Y is last digit of year, DDD is day of year, SSSS is a sequence number for the number of orders in that day. should be estimated magnitude+1).
I would store Tax info at the item level -- not all items are taxable, and some items have different tax rates. You can make your view containing the totals really easily:
SELECT O.[OrderId], ..., I.Item_Amount, I.Tax_Total,
I.Item_Amount + I.Tax_Total + O.Shipping_Amount AS Grand_Total
FROM [Orders] O INNER JOIN
(SELECT [OrderId]
SUM([Item_Amount]) AS [Sub_Total],
SUM([Tax_Amount]) AS [Tax_Total]
FROM [Order_Items]
GROUP BY [OrderId]) OI ON O.OrderId = I.OrderId
[Payments] - Wrong name; should be StoredCreditCards, or something to that effect. Payments implies that it contains payments. The primary key should be (CustId, StoredCreditCards_Seq), Seq being a sequential number that starts at one for each customer. Or you could have the customers name their cards. Whjatever.
[Products] - I would also recommend coming up with something other than ProductId as your PK. Is there no SKU to use?
[ShoppingCart] - RecordID is completely unecessary. The PK should be CartID. You may want to consider making a random number/code up for Cart identifiers (I use LEFT(CAST(NEWID() AS VARCHAR(32)),8) By making them sequential and predicable, people can edit thgeir cookies and take control of other carts.
[ShoppingCart_Toppings] - Again record id is unecessary. PK Should be (CartId, ProductId).
|||Alex,Thanks for your suggestions.
We are looking into it. I don't know if you looked into the revised schema I posted or not.
[AddressBook]:
- We will rename it
- We can't use CustID + AddressType as PK. Since this is a shopping, person can have more than one Shipping or Billing Address. (Something similar to Amazon or BN who allows you to store multiple shipping and billing address)
[Categories]:
- Client Defined. We removed Identity Column and used Category Name as PK
[Customers]:
- Made change as per your suggestion
[DeliveryZip]
- Looking into it
[Log]:
- Error Logs.
[Orders]:
- Will have to think if we can do that or go with Identity starting with 100000 or something.
[Payments]:
- I think we don't need to rename it. Payment actually stores Credit Card info and amount charged on their Credit card. We never show customer their Credit Card info upon their next visit. Payment ID is referenced in Orders Table to know how customer has paid and how much was charged to him including everything.
Correct me if I am wrong.
[Products]:
- Client doesn't have SKU for products. We may go with Identity column starting with 1000 or something.
[ShoppingCart]:
- CartID is not sequential. It is random.
- CartID can't be PK coz, if I add more than 1 item into my cart, CartID is going to get repeated.
E.g.
CartID ProductID Qty
AAAA 1000 2
AAAA 2012 1
-- We have used IBuySpy Portal as our reference and some of the design is based on that. This indicates to us that we can't depend on design like that. Correct?
Database Design & Normalization Question
How far should I go with normalizing my database? I'm
designing a database for products (books, music, videos,
etc.). I'm also designing a .Net front-end app for
managing the products in the SQL database. My frustration
is having to take the normalized data and convert it back
to a usable form for the management screens and then
saving it back to the normalized database. Here's an
example of the design:
I created a table named "Products" as a master table for
product information: Products(ItemNumber, Title, Retail)
I created a table named "TitleTypes" that holds a list of
different types of titles (Sub-Title, Foreign Title, Misc
Title, Exact Title, etc.): TitleTypes(TypeID, Description)
I created a table named "Products_AdditionalTitles" to
hold a list of additional titles for a given product and
what type of title it is. This table is related to the
two tables above based on the ItemNumber and TypeID
fields: Products_AdditionalTitles(ItemNumber,TypeID,Title)
Some books have sub-titles, foreign titles, etc. and some
don't so this allows each book to have an many or as few
titles as needed without storing NULLS or empty strings
in the database for books that don't have values for
these fields.
Is this the correct way to design the database? Is it
worth the trouble to do it this way? It seems like it
would be alot easier for INSERTS, UPDATES, and SELECTS to
have the extra title fields as part of the Products
database and just store empty strings in them if I don't
have a value.
Just want to confirm that I'm heading in the right
direction.
Thanks!>> How far should I go with normalizing my database?
As far as you can. In other words, full normalization upto BCNF in case of
tables with single column keys ( which will be in 5NF anyway ) and upto 5NF
in case of tables with composite keys and overlapping sets of values in
different rows are mandatory to avoid data modification anomalies.
>> Is this the correct way to design the database? Is it worth the trouble
>> to do it this way?
Without a detailed knowledge of your business model and existing entity
types, its attributes and applicable relationships others cannot comment on
a particular design narrative. If you are familiar with higher normal forms
beyond 1NF, every principled decomposition to avoid moification anomalies is
definitely worth the "trouble".
BTW, NULLs have nothing to do with any normal forms beyond 1NF.
>> It seems like it would be alot easier for INSERTS, UPDATES, and SELECTS
>> to have the extra title fields as part of the Products database and just
>> store empty strings in them if I don't have a value.
"Ease" of writing INSERTS, UPDATES, and SELECTS is not a design principle;
but integrity preservation is. You wouldn't consider a single table for
representing the entire schema by such an assessment of ease, would you?
>> Just want to confirm that I'm heading in the right direction.
If you are considering full normalization in your logical design process,
you are on the right track.
--
Anith|||I agree with the assessment that no sound judgement may be passed on any
particular design without the full business rules the design was based on;
however, there are a few problems that crop up now and again.
First, database normalization is a classification mechanism that attempts to
model a real world system. It is a method of logically seperating out
individually defined "nouns" or entities.
What I often see is that developers new to database design tend to apply
leasons learned from OOP/OOA to data modeling. Database design is a
reductionist exercise to classify and reduce data redundancy. OOP/OOA is an
aggregation of process consolidation whereby a user, by generalization, may
abstract out common features. These are at two polar extremes, or orthogonal
to one another. Be careful.
The first sign that you may have strayed too far is when you start building
MUCK tables and overly use Many-to-Many relationships. Everything is a
"type" with an ID, Name, and description, right? WRONG! Don't go down that
track.
Sincerely,
Anthony Thomas
"Anith Sen" wrote:
> >> How far should I go with normalizing my database?
> As far as you can. In other words, full normalization upto BCNF in case of
> tables with single column keys ( which will be in 5NF anyway ) and upto 5NF
> in case of tables with composite keys and overlapping sets of values in
> different rows are mandatory to avoid data modification anomalies.
> >> Is this the correct way to design the database? Is it worth the trouble
> >> to do it this way?
> Without a detailed knowledge of your business model and existing entity
> types, its attributes and applicable relationships others cannot comment on
> a particular design narrative. If you are familiar with higher normal forms
> beyond 1NF, every principled decomposition to avoid moification anomalies is
> definitely worth the "trouble".
> BTW, NULLs have nothing to do with any normal forms beyond 1NF.
> >> It seems like it would be alot easier for INSERTS, UPDATES, and SELECTS
> >> to have the extra title fields as part of the Products database and just
> >> store empty strings in them if I don't have a value.
> "Ease" of writing INSERTS, UPDATES, and SELECTS is not a design principle;
> but integrity preservation is. You wouldn't consider a single table for
> representing the entire schema by such an assessment of ease, would you?
> >> Just want to confirm that I'm heading in the right direction.
> If you are considering full normalization in your logical design process,
> you are on the right track.
> --
> Anith
>
>|||Perhaps Anith is more meticulous than I , but I generally go to 3rd Normal
Form, and call it a day...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jason Hedges" <jasonh@.bsmgr.com> wrote in message
news:557901c4c6b0$721c0840$a301280a@.phx.gbl...
> How far should I go with normalizing my database? I'm
> designing a database for products (books, music, videos,
> etc.). I'm also designing a .Net front-end app for
> managing the products in the SQL database. My frustration
> is having to take the normalized data and convert it back
> to a usable form for the management screens and then
> saving it back to the normalized database. Here's an
> example of the design:
> I created a table named "Products" as a master table for
> product information: Products(ItemNumber, Title, Retail)
> I created a table named "TitleTypes" that holds a list of
> different types of titles (Sub-Title, Foreign Title, Misc
> Title, Exact Title, etc.): TitleTypes(TypeID, Description)
> I created a table named "Products_AdditionalTitles" to
> hold a list of additional titles for a given product and
> what type of title it is. This table is related to the
> two tables above based on the ItemNumber and TypeID
> fields: Products_AdditionalTitles(ItemNumber,TypeID,Title)
> Some books have sub-titles, foreign titles, etc. and some
> don't so this allows each book to have an many or as few
> titles as needed without storing NULLS or empty strings
> in the database for books that don't have values for
> these fields.
> Is this the correct way to design the database? Is it
> worth the trouble to do it this way? It seems like it
> would be alot easier for INSERTS, UPDATES, and SELECTS to
> have the extra title fields as part of the Products
> database and just store empty strings in them if I don't
> have a value.
> Just want to confirm that I'm heading in the right
> direction.
> Thanks!|||Ok, maybe I've travelled too far down the "everything is
a type" road already.
Here's the scenario: I am designing a new database and
application in SQL Server and .Net for managing product
information for bookstores (strictly product data, not
sales, customers, etc.). The current database and
application have been developed with COBOL. The main
types of products we deal with are books, music, bibles,
videos, software, and gifts. Every product has some
common attributes (item number, title, retail price,
vendor) and some products have additional attributes (sub-
title, measurements, publish date, release date, foreign
language title, etc.). Products can have from 1 to 3
contributors (author, artist) and be categorized in 1 to
3 categories.
I've ended up with alot of many-to-many tables. For
example, I have the main products tables, a master
category table, and a many-to-many table that links
products with multiple categories. Same thing for
contributors. Another example: since there are several
types of dates that can be stored for a product (publish
date, release date, etc.), I have a "Date Type" table and
a many-to-many table that holds a list of dates and their
respective "date type" and the product item number they
belong to. I thought this would be the best way since
the number of each was an unknown.
I was under the impression that having a lot of fields in
a table that were not used was poor design. Example:
having a SubTitle field in my main product database would
be poor design since many products don't have a sub-
title. Is that true or false?
I'd appreciate some more input if you have enough info
here to go on.
Thanks!
Jason
>--Original Message--
>I agree with the assessment that no sound judgement may
be passed on any
>particular design without the full business rules the
design was based on;
>however, there are a few problems that crop up now and
again.
>First, database normalization is a classification
mechanism that attempts to
>model a real world system. It is a method of logically
seperating out
>individually defined "nouns" or entities.
>What I often see is that developers new to database
design tend to apply
>leasons learned from OOP/OOA to data modeling. Database
design is a
>reductionist exercise to classify and reduce data
redundancy. OOP/OOA is an
>aggregation of process consolidation whereby a user, by
generalization, may
>abstract out common features. These are at two polar
extremes, or orthogonal
>to one another. Be careful.
>The first sign that you may have strayed too far is when
you start building
>MUCK tables and overly use Many-to-Many relationships.
Everything is a
>"type" with an ID, Name, and description, right?
WRONG! Don't go down that
>track.
>Sincerely,
>
>Anthony Thomas
>
>"Anith Sen" wrote:
>> >> How far should I go with normalizing my database?
>> As far as you can. In other words, full normalization
upto BCNF in case of
>> tables with single column keys ( which will be in 5NF
anyway ) and upto 5NF
>> in case of tables with composite keys and overlapping
sets of values in
>> different rows are mandatory to avoid data
modification anomalies.
>> >> Is this the correct way to design the database? Is
it worth the trouble
>> >> to do it this way?
>> Without a detailed knowledge of your business model
and existing entity
>> types, its attributes and applicable relationships
others cannot comment on
>> a particular design narrative. If you are familiar
with higher normal forms
>> beyond 1NF, every principled decomposition to avoid
moification anomalies is
>> definitely worth the "trouble".
>> BTW, NULLs have nothing to do with any normal forms
beyond 1NF.
>> >> It seems like it would be alot easier for INSERTS,
UPDATES, and SELECTS
>> >> to have the extra title fields as part of the
Products database and just
>> >> store empty strings in them if I don't have a value.
>> "Ease" of writing INSERTS, UPDATES, and SELECTS is not
a design principle;
>> but integrity preservation is. You wouldn't consider a
single table for
>> representing the entire schema by such an assessment
of ease, would you?
>> >> Just want to confirm that I'm heading in the right
direction.
>> If you are considering full normalization in your
logical design process,
>> you are on the right track.
>> --
>> Anith
>>
>.
>|||"Jason Hedges" <jasonh@.bsmgr.com> wrote in message
news:505a01c4c737$16d9aeb0$a601280a@.phx.gbl...
I'm very interested in this discussion, as I recently completed a
bibliography project that is somewhat similar to your bookstore project (see
http://mwilden.com/forester/).
I always start from the user interface. What does the user want to do on a
screen, or with a report. What buttons does he want to push? What rows and
columns of stuff does he want to see? A database is just a tool to achieve
that.
Note that this is quite different from saying that a database should model
or mirror a UI. But all database decisions must eventually come down to "how
does this help the user?".
What would be interesting is to know what your users expect from this
application. For example, do they need to search for subtitles? If so,
subtitle should clearly be a separate column (possibly in a separate table).
On the other hand, if they don't need to search, or otherwise need to
distinguish among, subtitles, why make it a separate column at all? Why not
just tack it on to the end of the title, as in modern library practice?
I'm not suggesting one or the other - just that you have to first find out
what the users expect from your system. There's no way to design a
"bookstore database" without considering such things. And normalization
(contrary to popular belief) must also be driven by these concerns. In one
database, normalization would require storing an address separately from its
city. In another, it would require storing only a latitude and longitude,
which would be related to a table of addresses. It depends on the
application.
> Every product has some
> common attributes (item number, title, retail price,
> vendor) and some products have additional attributes (sub-
> title, measurements, publish date, release date, foreign
> language title, etc.).
All products have measurements in the real world. Whether you want to record
them for all products depends on the user requirements. Similarly with all
the other characteristics you mention.
> Products can have from 1 to 3
> contributors (author, artist) and be categorized in 1 to
> 3 categories.
This may match your user's needs, but probably not. Lots of books have more
than three authors, to say nothing of editors, illustrators, dustjacket
artists, designers, etc.
> I have a "Date Type" table and
> a many-to-many table that holds a list of dates and their
> respective "date type" and the product item number they
> belong to. I thought this would be the best way since
> the number of each was an unknown.
However, the upper bound of such is known. Every book does indeed have a
first French publication date - in most cases, however, that value is NULL.
I don't see a problem with modelling that fact in a column instead of a
relationship. The thing is, you're going to have to LEFT JOIN your m-to-m
table to get back the NULL at some point anyway.
On the other hand, if you want to model editions specifically (as I did in
my C.S. Forester database), you won't want to store the French publication
date in one table and the French title in another - you'll need an Editions
table that records this information (and probably more) in one place. It
depends on what the users want.
> I was under the impression that having a lot of fields in
> a table that were not used was poor design. Example:
> having a SubTitle field in my main product database would
> be poor design since many products don't have a sub-
> title. Is that true or false?
Again, it depends how you want to use the subtitle concept. Odds are (I'm
guessing), you want to display the subtitle of every book at some point.
Instead of linking to a table to do this, why not just display its column
(even if it's NULL)? (A variable-length column takes almost no space if it's
NULL.)|||Mark is on a different path, although, headed in the right direction.
First, you need to understand this, the goals of database design and those
of application design are predicated on two different, sometimes opposing,
sometimes cooperative, criteria. Applications are designed for data
MANIPULATION and PROCESS. Databases, however, are designed for data
INTEGRITY and IDENTIFICATION.
In the sense that you must understand what the User wants is absolutely
correct. For the application, the GUI and the process of information is a
perfectly acceptable viewpoint to model the appliction design and process.
Howerver, this is completely wrong for the design of the database system.
I am assuming that many of those "unique" attributes are based on the
product "type." If there are truely common caracteristics of Entity Classes,
then perhaps a Super Table Subordinate Table relation would work better,
although the current SQL DBMSes do not fully support this sort of structure
well.
The idea is that there are Products, which have common attributes, but,
then, there are Books that are dissimilar from software and videos. Now, you
can call all of these Products, but, then, we must deal with the type
specific attributes. All in the same table allowed NULL? Seperate table and
make entries for only those that have data? For all of the unique columns or
only some because it is likely that you will only populate some base on type.
Aha, sounds like a subordinate class. You create a Book table for the Book
entity, a Video table for the Video entity, etc, each with its own specific
attributes. You can tie all of these back to a common Products table to
carry the common attributes if you wish. The point is that Books are not
Videos are not Software, even if they do have common characteristics. Just
because humans and fish have eyes and mouths does not make a human a fish nor
a fish a human. Aha, but they are both animals. See the structure?
The point is that data modeling comes down to describing the real world, not
as a process, but as a classification, for delineating, reducing, individual
attributes.
I think you basically had the right ideas for most of what you described;
however, take a look at the seperate or super-sub table structure. But the
dates, no, that is where you started getting into the MUCK area. A
pulication date is not a release date any more than a start time is the same
as an end time. Is your birthdate the same as your death date? I certainly
hope not!
Forget about all of that garbage about normalization performance. Research
has shown that the proper normalization of data to ensure the integrity and
reduce the duplicity of data is by far the best means to garauntee the best
performance. That is why we have DBMSes, to maximize the performance GIVEN a
normalized database. A DBMS is a physical mechanism, the data model and its
normalization is a logical one. The two are indepenent, although related, or
better, derived. That is, the physical is derived from the logical, not the
other way around. So, performance should never be a constraint on the
design, only the integrity of the system.
Feel free to follow up if you think we could provide you with any further
assistance.
Check out "An Introduction to Database Systems" by C. J. Date. I think you
would find it enlightening.
Sincerely,
Anthony Thomas
"Jason Hedges" wrote:
> Ok, maybe I've travelled too far down the "everything is
> a type" road already.
> Here's the scenario: I am designing a new database and
> application in SQL Server and .Net for managing product
> information for bookstores (strictly product data, not
> sales, customers, etc.). The current database and
> application have been developed with COBOL. The main
> types of products we deal with are books, music, bibles,
> videos, software, and gifts. Every product has some
> common attributes (item number, title, retail price,
> vendor) and some products have additional attributes (sub-
> title, measurements, publish date, release date, foreign
> language title, etc.). Products can have from 1 to 3
> contributors (author, artist) and be categorized in 1 to
> 3 categories.
> I've ended up with alot of many-to-many tables. For
> example, I have the main products tables, a master
> category table, and a many-to-many table that links
> products with multiple categories. Same thing for
> contributors. Another example: since there are several
> types of dates that can be stored for a product (publish
> date, release date, etc.), I have a "Date Type" table and
> a many-to-many table that holds a list of dates and their
> respective "date type" and the product item number they
> belong to. I thought this would be the best way since
> the number of each was an unknown.
> I was under the impression that having a lot of fields in
> a table that were not used was poor design. Example:
> having a SubTitle field in my main product database would
> be poor design since many products don't have a sub-
> title. Is that true or false?
> I'd appreciate some more input if you have enough info
> here to go on.
> Thanks!
> Jason
> >--Original Message--
> >I agree with the assessment that no sound judgement may
> be passed on any
> >particular design without the full business rules the
> design was based on;
> >however, there are a few problems that crop up now and
> again.
> >
> >First, database normalization is a classification
> mechanism that attempts to
> >model a real world system. It is a method of logically
> seperating out
> >individually defined "nouns" or entities.
> >
> >What I often see is that developers new to database
> design tend to apply
> >leasons learned from OOP/OOA to data modeling. Database
> design is a
> >reductionist exercise to classify and reduce data
> redundancy. OOP/OOA is an
> >aggregation of process consolidation whereby a user, by
> generalization, may
> >abstract out common features. These are at two polar
> extremes, or orthogonal
> >to one another. Be careful.
> >
> >The first sign that you may have strayed too far is when
> you start building
> >MUCK tables and overly use Many-to-Many relationships.
> Everything is a
> >"type" with an ID, Name, and description, right?
> WRONG! Don't go down that
> >track.
> >
> >Sincerely,
> >
> >
> >Anthony Thomas
> >
> >
> >"Anith Sen" wrote:
> >
> >> >> How far should I go with normalizing my database?
> >>
> >> As far as you can. In other words, full normalization
> upto BCNF in case of
> >> tables with single column keys ( which will be in 5NF
> anyway ) and upto 5NF
> >> in case of tables with composite keys and overlapping
> sets of values in
> >> different rows are mandatory to avoid data
> modification anomalies.
> >>
> >> >> Is this the correct way to design the database? Is
> it worth the trouble
> >> >> to do it this way?
> >>
> >> Without a detailed knowledge of your business model
> and existing entity
> >> types, its attributes and applicable relationships
> others cannot comment on
> >> a particular design narrative. If you are familiar
> with higher normal forms
> >> beyond 1NF, every principled decomposition to avoid
> moification anomalies is
> >> definitely worth the "trouble".
> >>
> >> BTW, NULLs have nothing to do with any normal forms
> beyond 1NF.
> >>
> >> >> It seems like it would be alot easier for INSERTS,
> UPDATES, and SELECTS
> >> >> to have the extra title fields as part of the
> Products database and just
> >> >> store empty strings in them if I don't have a value.
> >>
> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is not
> a design principle;
> >> but integrity preservation is. You wouldn't consider a
> single table for
> >> representing the entire schema by such an assessment
> of ease, would you?
> >>
> >> >> Just want to confirm that I'm heading in the right
> direction.
> >>
> >> If you are considering full normalization in your
> logical design process,
> >> you are on the right track.
> >>
> >> --
> >> Anith
> >>
> >>
> >>
> >.
> >
>|||Anthony,
Thanks for your reply and the book recommendation.
Can you give me more direction on the date issue? I
understand the logic behind the books, videos, software,
etc. What do you do with data like the date fields?
Should each date type (pub date, street date, etc.) be a
separate field/column in a table? I thought the way I had
done it allowed for alot of flexibility because you
didn't have to change the database design to store new
types of data that were similar to other types already
being stored. Here's another example (similar to the date
scenario):
I have a table that defines "title types" (sub title,
foreign title, title as it appears on the product, etc.).
I have a many-to-many relationship table that stores a
product identifier (relating back to the master product
table), a title, and a title type identifer (relating to
the title types table). This seemed flexible since I can
decide to store a "misc title" simply by adding
another "title type" to my title types table and using
it's identifer.
How should I be storing this type of data? Should each
type of title be a separate field in a table? Should I
just store NULLS or empty strings when I don't have a
value for one of these fields?
Thanks!
Jason
>--Original Message--
>Mark is on a different path, although, headed in the
right direction.
>First, you need to understand this, the goals of
database design and those
>of application design are predicated on two different,
sometimes opposing,
>sometimes cooperative, criteria. Applications are
designed for data
>MANIPULATION and PROCESS. Databases, however, are
designed for data
>INTEGRITY and IDENTIFICATION.
>In the sense that you must understand what the User
wants is absolutely
>correct. For the application, the GUI and the process
of information is a
>perfectly acceptable viewpoint to model the appliction
design and process.
>Howerver, this is completely wrong for the design of the
database system.
>I am assuming that many of those "unique" attributes are
based on the
>product "type." If there are truely common
caracteristics of Entity Classes,
>then perhaps a Super Table Subordinate Table relation
would work better,
>although the current SQL DBMSes do not fully support
this sort of structure
>well.
>The idea is that there are Products, which have common
attributes, but,
>then, there are Books that are dissimilar from software
and videos. Now, you
>can call all of these Products, but, then, we must deal
with the type
>specific attributes. All in the same table allowed
NULL? Seperate table and
>make entries for only those that have data? For all of
the unique columns or
>only some because it is likely that you will only
populate some base on type.
> Aha, sounds like a subordinate class. You create a
Book table for the Book
>entity, a Video table for the Video entity, etc, each
with its own specific
>attributes. You can tie all of these back to a common
Products table to
>carry the common attributes if you wish. The point is
that Books are not
>Videos are not Software, even if they do have common
characteristics. Just
>because humans and fish have eyes and mouths does not
make a human a fish nor
>a fish a human. Aha, but they are both animals. See
the structure?
>The point is that data modeling comes down to describing
the real world, not
>as a process, but as a classification, for delineating,
reducing, individual
>attributes.
>I think you basically had the right ideas for most of
what you described;
>however, take a look at the seperate or super-sub table
structure. But the
>dates, no, that is where you started getting into the
MUCK area. A
>pulication date is not a release date any more than a
start time is the same
>as an end time. Is your birthdate the same as your
death date? I certainly
>hope not!
>Forget about all of that garbage about normalization
performance. Research
>has shown that the proper normalization of data to
ensure the integrity and
>reduce the duplicity of data is by far the best means to
garauntee the best
>performance. That is why we have DBMSes, to maximize
the performance GIVEN a
>normalized database. A DBMS is a physical mechanism,
the data model and its
>normalization is a logical one. The two are indepenent,
although related, or
>better, derived. That is, the physical is derived from
the logical, not the
>other way around. So, performance should never be a
constraint on the
>design, only the integrity of the system.
>Feel free to follow up if you think we could provide you
with any further
>assistance.
>Check out "An Introduction to Database Systems" by C. J.
Date. I think you
>would find it enlightening.
>Sincerely,
>
>Anthony Thomas
>
>"Jason Hedges" wrote:
>> Ok, maybe I've travelled too far down the "everything
is
>> a type" road already.
>> Here's the scenario: I am designing a new database and
>> application in SQL Server and .Net for managing
product
>> information for bookstores (strictly product data, not
>> sales, customers, etc.). The current database and
>> application have been developed with COBOL. The main
>> types of products we deal with are books, music,
bibles,
>> videos, software, and gifts. Every product has some
>> common attributes (item number, title, retail price,
>> vendor) and some products have additional attributes
(sub-
>> title, measurements, publish date, release date,
foreign
>> language title, etc.). Products can have from 1 to 3
>> contributors (author, artist) and be categorized in 1
to
>> 3 categories.
>> I've ended up with alot of many-to-many tables. For
>> example, I have the main products tables, a master
>> category table, and a many-to-many table that links
>> products with multiple categories. Same thing for
>> contributors. Another example: since there are several
>> types of dates that can be stored for a product
(publish
>> date, release date, etc.), I have a "Date Type" table
and
>> a many-to-many table that holds a list of dates and
their
>> respective "date type" and the product item number
they
>> belong to. I thought this would be the best way since
>> the number of each was an unknown.
>> I was under the impression that having a lot of fields
in
>> a table that were not used was poor design. Example:
>> having a SubTitle field in my main product database
would
>> be poor design since many products don't have a sub-
>> title. Is that true or false?
>> I'd appreciate some more input if you have enough info
>> here to go on.
>> Thanks!
>> Jason
>> >--Original Message--
>> >I agree with the assessment that no sound judgement
may
>> be passed on any
>> >particular design without the full business rules the
>> design was based on;
>> >however, there are a few problems that crop up now
and
>> again.
>> >
>> >First, database normalization is a classification
>> mechanism that attempts to
>> >model a real world system. It is a method of
logically
>> seperating out
>> >individually defined "nouns" or entities.
>> >
>> >What I often see is that developers new to database
>> design tend to apply
>> >leasons learned from OOP/OOA to data modeling.
Database
>> design is a
>> >reductionist exercise to classify and reduce data
>> redundancy. OOP/OOA is an
>> >aggregation of process consolidation whereby a user,
by
>> generalization, may
>> >abstract out common features. These are at two polar
>> extremes, or orthogonal
>> >to one another. Be careful.
>> >
>> >The first sign that you may have strayed too far is
when
>> you start building
>> >MUCK tables and overly use Many-to-Many
relationships.
>> Everything is a
>> >"type" with an ID, Name, and description, right?
>> WRONG! Don't go down that
>> >track.
>> >
>> >Sincerely,
>> >
>> >
>> >Anthony Thomas
>> >
>> >
>> >"Anith Sen" wrote:
>> >
>> >> >> How far should I go with normalizing my database?
>> >>
>> >> As far as you can. In other words, full
normalization
>> upto BCNF in case of
>> >> tables with single column keys ( which will be in
5NF
>> anyway ) and upto 5NF
>> >> in case of tables with composite keys and
overlapping
>> sets of values in
>> >> different rows are mandatory to avoid data
>> modification anomalies.
>> >>
>> >> >> Is this the correct way to design the database?
Is
>> it worth the trouble
>> >> >> to do it this way?
>> >>
>> >> Without a detailed knowledge of your business model
>> and existing entity
>> >> types, its attributes and applicable relationships
>> others cannot comment on
>> >> a particular design narrative. If you are familiar
>> with higher normal forms
>> >> beyond 1NF, every principled decomposition to avoid
>> moification anomalies is
>> >> definitely worth the "trouble".
>> >>
>> >> BTW, NULLs have nothing to do with any normal forms
>> beyond 1NF.
>> >>
>> >> >> It seems like it would be alot easier for
INSERTS,
>> UPDATES, and SELECTS
>> >> >> to have the extra title fields as part of the
>> Products database and just
>> >> >> store empty strings in them if I don't have a
value.
>> >>
>> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is
not
>> a design principle;
>> >> but integrity preservation is. You wouldn't
consider a
>> single table for
>> >> representing the entire schema by such an
assessment
>> of ease, would you?
>> >>
>> >> >> Just want to confirm that I'm heading in the
right
>> direction.
>> >>
>> >> If you are considering full normalization in your
>> logical design process,
>> >> you are on the right track.
>> >>
>> >> --
>> >> Anith
>> >>
>> >>
>> >>
>> >.
>> >
>.
>|||Like Mark replied, it depends on the requirements. In that, I agree;
however, where our friend says he starts with User Interface and the process,
this is where I disagree. Although it does DEPEND, data models depend on ONE
thing, the BUSINESS MODEL, which should have reflacted the real world
structure.
You have asked two seperate questions here. So, let's start with the easier
one, the Titles. First of all, this is not a Many-to-Many relationship; it
is a One-to-Many-to-One relationship: Products, Other Titles, Title Types.
I would claim that the Title is an attribute of the Product; moreover, it is
a candidate key. I would require each of my products to have a title. How
else would you define it? Now, some products may have 1 or more Other
Titles, or Additional Titles. This sentence clues you into what kind of
structure this should have. "Some Products" implies a 0, 1, or N relation.
If it were simply 0 or 1, then a NULL attribute may be sufficient.
I tend shy away from NULL attributes and use subsidiary tables unless the
creation and management of that additional table would be overkill with
respect to the original attribute. My favorite example is the Middle Initial
attribute. Now, in this case, seperating that attribute out to a dependent
subsidiary table would be overkill.
In your case, however, this is what we will need. Why? Because, in all
likelihood, the potential table could be a very large attribute and may not
be queried very frequently; so, why embed such a thing in the original table?
In addition, you will want to potentially have Many additional or subsidiary
titles. This requires the seperation.
This table would be One or Many-to-One against the Product table. Then, you
will probably want to classify the titles based on type, restricted or
otherwise, in the sense that you may allow only one additional title per
type. This would spawn the process of the type table that would be in
One-to-Many correspondence with the Other Titles table.
Let me know if that does not make sense.
Now, the dates. Here is a reason to NOT split this out to a Many-to-Many
relationship: how would you query it? Many-to-Many relationships have a
tendancy, especially the "Type" table kinds, to turn columns (attribute
names) in to data rows. When that happens, you have turned a simple SELECT
column FROM table1 (or joined to table2) from a horridous WHERE clause where
you have to specify multiple filter commands just to discover the record you
are after BEFORE you can determine the "VALUE" column that contains the data.
I've seen this numerous times and it will kill your system.
Now, I do not want to backtrack by saying that Performance should overrule
design. NO. However, oftentimes bad design will kill performance, no matter
what you do to improve it. Proper design, even when it looks like a
performance killer at first (usually because of the induced additional joins)
will usually save your performance in the long run.
Here is what I mean. A table is a representation of an Entity--a thing, a
noun, a specific item--that "relates" all of the appropriate and dependent
characteristics of that Entity. Now, your products have dates.
Normalization would dictate that these dates would depend on the KEY, the
WHOLE KEY, and NOTHING BUT THE KEY. I will assume that your products have a
primary TITLE (?) as the identifying characteristic. Perhaps it is some sort
of combination, regardless if you have introduced some sort of machine
generated ID surrogate key.
Now, the question is: Are these Dates Entities on their own, or, are they
somehow dependent on the Product Entity? I can see at least two ways this
may be true, although there is surely others.
1. The dates are individually unique attributes. The misnaming of them as
Date1, Date2, etc. may cause someone to believe the table has now violated
1NF or 2NF; however, if those attributes are not generic and follow the KEY
rules, then they are perfectly acceptable as embedded attributes within the
Entity they are related to.
2. There is some form of functional or constrained dependancy between the
dates. This would indicate the prescence and need for higher forms of
normalization: Boyce-Codd NF, 4NF, 5NF, or the new 6NF. These may not be
collection of dates but Product Release Schedules, a different but related
Entity, assigned (or related) to the Product Entity, which have ScheduleDate
as one of their attributes.
Regardless, you will have to decide the classification of these but DO NOT
GENERICIZE the attributes into a Muck Table: ID, Name, Description with the
associated crosstab table with the Value attribute. Type tables are
necessary but not all inclusive. The Value attribute has little value and
should be an indicator that the Entity you have defined has been genericized.
An Entity only exists if it is well-defined and models a real world element,
not an abstracted OO class.
I hope this makes sense. Feel free to continue the conversation if it does
not.
Sincerely,
Anthony Thomas
"Jason Hedges" wrote:
> Anthony,
> Thanks for your reply and the book recommendation.
> Can you give me more direction on the date issue? I
> understand the logic behind the books, videos, software,
> etc. What do you do with data like the date fields?
> Should each date type (pub date, street date, etc.) be a
> separate field/column in a table? I thought the way I had
> done it allowed for alot of flexibility because you
> didn't have to change the database design to store new
> types of data that were similar to other types already
> being stored. Here's another example (similar to the date
> scenario):
> I have a table that defines "title types" (sub title,
> foreign title, title as it appears on the product, etc.).
> I have a many-to-many relationship table that stores a
> product identifier (relating back to the master product
> table), a title, and a title type identifer (relating to
> the title types table). This seemed flexible since I can
> decide to store a "misc title" simply by adding
> another "title type" to my title types table and using
> it's identifer.
> How should I be storing this type of data? Should each
> type of title be a separate field in a table? Should I
> just store NULLS or empty strings when I don't have a
> value for one of these fields?
> Thanks!
> Jason
> >--Original Message--
> >Mark is on a different path, although, headed in the
> right direction.
> >
> >First, you need to understand this, the goals of
> database design and those
> >of application design are predicated on two different,
> sometimes opposing,
> >sometimes cooperative, criteria. Applications are
> designed for data
> >MANIPULATION and PROCESS. Databases, however, are
> designed for data
> >INTEGRITY and IDENTIFICATION.
> >
> >In the sense that you must understand what the User
> wants is absolutely
> >correct. For the application, the GUI and the process
> of information is a
> >perfectly acceptable viewpoint to model the appliction
> design and process.
> >Howerver, this is completely wrong for the design of the
> database system.
> >
> >I am assuming that many of those "unique" attributes are
> based on the
> >product "type." If there are truely common
> caracteristics of Entity Classes,
> >then perhaps a Super Table Subordinate Table relation
> would work better,
> >although the current SQL DBMSes do not fully support
> this sort of structure
> >well.
> >
> >The idea is that there are Products, which have common
> attributes, but,
> >then, there are Books that are dissimilar from software
> and videos. Now, you
> >can call all of these Products, but, then, we must deal
> with the type
> >specific attributes. All in the same table allowed
> NULL? Seperate table and
> >make entries for only those that have data? For all of
> the unique columns or
> >only some because it is likely that you will only
> populate some base on type.
> > Aha, sounds like a subordinate class. You create a
> Book table for the Book
> >entity, a Video table for the Video entity, etc, each
> with its own specific
> >attributes. You can tie all of these back to a common
> Products table to
> >carry the common attributes if you wish. The point is
> that Books are not
> >Videos are not Software, even if they do have common
> characteristics. Just
> >because humans and fish have eyes and mouths does not
> make a human a fish nor
> >a fish a human. Aha, but they are both animals. See
> the structure?
> >
> >The point is that data modeling comes down to describing
> the real world, not
> >as a process, but as a classification, for delineating,
> reducing, individual
> >attributes.
> >
> >I think you basically had the right ideas for most of
> what you described;
> >however, take a look at the seperate or super-sub table
> structure. But the
> >dates, no, that is where you started getting into the
> MUCK area. A
> >pulication date is not a release date any more than a
> start time is the same
> >as an end time. Is your birthdate the same as your
> death date? I certainly
> >hope not!
> >
> >Forget about all of that garbage about normalization
> performance. Research
> >has shown that the proper normalization of data to
> ensure the integrity and
> >reduce the duplicity of data is by far the best means to
> garauntee the best
> >performance. That is why we have DBMSes, to maximize
> the performance GIVEN a
> >normalized database. A DBMS is a physical mechanism,
> the data model and its
> >normalization is a logical one. The two are indepenent,
> although related, or
> >better, derived. That is, the physical is derived from
> the logical, not the
> >other way around. So, performance should never be a
> constraint on the
> >design, only the integrity of the system.
> >
> >Feel free to follow up if you think we could provide you
> with any further
> >assistance.
> >
> >Check out "An Introduction to Database Systems" by C. J.
> Date. I think you
> >would find it enlightening.
> >
> >Sincerely,
> >
> >
> >Anthony Thomas
> >
> >
> >"Jason Hedges" wrote:
> >
> >>
> >> Ok, maybe I've travelled too far down the "everything
> is
> >> a type" road already.
> >>
> >> Here's the scenario: I am designing a new database and
> >> application in SQL Server and .Net for managing
> product
> >> information for bookstores (strictly product data, not
> >> sales, customers, etc.). The current database and
> >> application have been developed with COBOL. The main
> >> types of products we deal with are books, music,
> bibles,
> >> videos, software, and gifts. Every product has some
> >> common attributes (item number, title, retail price,
> >> vendor) and some products have additional attributes
> (sub-
> >> title, measurements, publish date, release date,
> foreign
> >> language title, etc.). Products can have from 1 to 3
> >> contributors (author, artist) and be categorized in 1
> to
> >> 3 categories.
> >>
> >> I've ended up with alot of many-to-many tables. For
> >> example, I have the main products tables, a master
> >> category table, and a many-to-many table that links
> >> products with multiple categories. Same thing for
> >> contributors. Another example: since there are several
> >> types of dates that can be stored for a product
> (publish
> >> date, release date, etc.), I have a "Date Type" table
> and
> >> a many-to-many table that holds a list of dates and
> their
> >> respective "date type" and the product item number
> they
> >> belong to. I thought this would be the best way since
> >> the number of each was an unknown.
> >>
> >> I was under the impression that having a lot of fields
> in
> >> a table that were not used was poor design. Example:
> >> having a SubTitle field in my main product database
> would
> >> be poor design since many products don't have a sub-
> >> title. Is that true or false?
> >>
> >> I'd appreciate some more input if you have enough info
> >> here to go on.
> >>
> >> Thanks!
> >> Jason
> >>
> >> >--Original Message--
> >> >I agree with the assessment that no sound judgement
> may
> >> be passed on any
> >> >particular design without the full business rules the
> >> design was based on;
> >> >however, there are a few problems that crop up now
> and
> >> again.
> >> >
> >> >First, database normalization is a classification
> >> mechanism that attempts to
> >> >model a real world system. It is a method of
> logically
> >> seperating out
> >> >individually defined "nouns" or entities.
> >> >
> >> >What I often see is that developers new to database
> >> design tend to apply
> >> >leasons learned from OOP/OOA to data modeling.
> Database
> >> design is a
> >> >reductionist exercise to classify and reduce data
> >> redundancy. OOP/OOA is an
> >> >aggregation of process consolidation whereby a user,
> by
> >> generalization, may
> >> >abstract out common features. These are at two polar
> >> extremes, or orthogonal
> >> >to one another. Be careful.
> >> >
> >> >The first sign that you may have strayed too far is
> when
> >> you start building
> >> >MUCK tables and overly use Many-to-Many
> relationships.
> >> Everything is a
> >> >"type" with an ID, Name, and description, right?
> >> WRONG! Don't go down that
> >> >track.
> >> >
> >> >Sincerely,
> >> >
> >> >
> >> >Anthony Thomas
> >> >
> >> >
> >> >"Anith Sen" wrote:
> >> >
> >> >> >> How far should I go with normalizing my database?
> >> >>
> >> >> As far as you can. In other words, full
> normalization
> >> upto BCNF in case of
> >> >> tables with single column keys ( which will be in
> 5NF
> >> anyway ) and upto 5NF
> >> >> in case of tables with composite keys and
> overlapping
> >> sets of values in
> >> >> different rows are mandatory to avoid data
> >> modification anomalies.
> >> >>
> >> >> >> Is this the correct way to design the database?
> Is
> >> it worth the trouble
> >> >> >> to do it this way?
> >> >>
> >> >> Without a detailed knowledge of your business model
> >> and existing entity
> >> >> types, its attributes and applicable relationships
> >> others cannot comment on
> >> >> a particular design narrative. If you are familiar
> >> with higher normal forms
> >> >> beyond 1NF, every principled decomposition to avoid
> >> moification anomalies is
> >> >> definitely worth the "trouble".
> >> >>
> >> >> BTW, NULLs have nothing to do with any normal forms
> >> beyond 1NF.
> >> >>
> >> >> >> It seems like it would be alot easier for
> INSERTS,
> >> UPDATES, and SELECTS
> >> >> >> to have the extra title fields as part of the
> >> Products database and just
> >> >> >> store empty strings in them if I don't have a
> value.
> >> >>
> >> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is
> not
> >> a design principle;
> >> >> but integrity preservation is. You wouldn't
> consider a
> >> single table for
> >> >> representing the entire schema by such an
> assessment
> >> of ease, would you?
> >> >>
> >> >> >> Just want to confirm that I'm heading in the
> right
> >> direction.
> >> >>
> >> >> If you are considering full normalization in your
> >> logical design process,
> >> >> you are on the right track.
> >> >>
> >> >> --
> >> >> Anith
> >> >>
> >> >>
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>|||"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:AD17439F-180F-49E2-BB21-1DECA363C834@.microsoft.com...
> Like Mark replied, it depends on the requirements. In that, I agree;
> however, where our friend says he starts with User Interface and the
process,
> this is where I disagree.
Just to be clear, I mean that the user requirements drive the database, not
the "real world." The real world is too big to model. The hard part (that I
think our other friend needs to better define) is what part of that real
world is important to his users. Since all the users care about is what they
see on the screen or on a bit of paper, starting from the user interface is
usually the best way to determine what part of the real world needs to be
modelled.
Take subtitles, for example. If the user requires a screen that lets him
search for subtitles, then clearly, subtitles need to be in a column of
their own. If such a search (= screen) isn't required, subtitles could
simply be included in the title. The real world (alone) doesn't allow one to
make this choice.
> I would claim that the Title is an attribute of the Product; moreover, it
is
> a candidate key.
Actually, titles aren't unique (in the real world:).
> My favorite example is the Middle Initial
> attribute. Now, in this case, seperating that attribute out to a
dependent
> subsidiary table would be overkill.
But in "the real world," middle initial is a separate piece of information
from the rest of the name. What defines "overkill" is not the real world,
but the user requirements.
I do agree with most of your points, however.|||Then, perhaps, we agree more than what we've originally indicated. And, to
our friend with the problem, this isn't easy.
The point is that it is impossible for anyone out here to give you specific
design criteria. For that, the Database Engineer MUST BE very close the
end-user requirements.
Lastly, all I would say to the "real world" model--a rather too loose term,
I agree--is that data modeling is more an intent of data definition; whereas,
application design is more intent on process modling. At some point, the two
will need to work together, but it must be recognized that the design goals
of each are targeted at two different aspects of the same project.
Oftentimes, these targets are divergent.
Sincerely,
Anthony Thomas
"Mark Wilden" wrote:
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:AD17439F-180F-49E2-BB21-1DECA363C834@.microsoft.com...
> > Like Mark replied, it depends on the requirements. In that, I agree;
> > however, where our friend says he starts with User Interface and the
> process,
> > this is where I disagree.
> Just to be clear, I mean that the user requirements drive the database, not
> the "real world." The real world is too big to model. The hard part (that I
> think our other friend needs to better define) is what part of that real
> world is important to his users. Since all the users care about is what they
> see on the screen or on a bit of paper, starting from the user interface is
> usually the best way to determine what part of the real world needs to be
> modelled.
> Take subtitles, for example. If the user requires a screen that lets him
> search for subtitles, then clearly, subtitles need to be in a column of
> their own. If such a search (= screen) isn't required, subtitles could
> simply be included in the title. The real world (alone) doesn't allow one to
> make this choice.
> > I would claim that the Title is an attribute of the Product; moreover, it
> is
> > a candidate key.
> Actually, titles aren't unique (in the real world:).
> > My favorite example is the Middle Initial
> > attribute. Now, in this case, seperating that attribute out to a
> dependent
> > subsidiary table would be overkill.
> But in "the real world," middle initial is a separate piece of information
> from the rest of the name. What defines "overkill" is not the real world,
> but the user requirements.
> I do agree with most of your points, however.
>
>|||"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:8C4043F6-1E5D-4DEA-9DA6-B5E4E911E7AD@.microsoft.com...
> Lastly, all I would say to the "real world" model--a rather too loose
term,
> I agree--is that data modeling is more an intent of data definition;
whereas,
> application design is more intent on process modling. At some point, the
two
> will need to work together, but it must be recognized that the design
goals
> of each are targeted at two different aspects of the same project.
> Oftentimes, these targets are divergent.
Good points.
designing a database for products (books, music, videos,
etc.). I'm also designing a .Net front-end app for
managing the products in the SQL database. My frustration
is having to take the normalized data and convert it back
to a usable form for the management screens and then
saving it back to the normalized database. Here's an
example of the design:
I created a table named "Products" as a master table for
product information: Products(ItemNumber, Title, Retail)
I created a table named "TitleTypes" that holds a list of
different types of titles (Sub-Title, Foreign Title, Misc
Title, Exact Title, etc.): TitleTypes(TypeID, Description)
I created a table named "Products_AdditionalTitles" to
hold a list of additional titles for a given product and
what type of title it is. This table is related to the
two tables above based on the ItemNumber and TypeID
fields: Products_AdditionalTitles(ItemNumber,TypeID,Title)
Some books have sub-titles, foreign titles, etc. and some
don't so this allows each book to have an many or as few
titles as needed without storing NULLS or empty strings
in the database for books that don't have values for
these fields.
Is this the correct way to design the database? Is it
worth the trouble to do it this way? It seems like it
would be alot easier for INSERTS, UPDATES, and SELECTS to
have the extra title fields as part of the Products
database and just store empty strings in them if I don't
have a value.
Just want to confirm that I'm heading in the right
direction.
Thanks!>> How far should I go with normalizing my database?
As far as you can. In other words, full normalization upto BCNF in case of
tables with single column keys ( which will be in 5NF anyway ) and upto 5NF
in case of tables with composite keys and overlapping sets of values in
different rows are mandatory to avoid data modification anomalies.
>> Is this the correct way to design the database? Is it worth the trouble
>> to do it this way?
Without a detailed knowledge of your business model and existing entity
types, its attributes and applicable relationships others cannot comment on
a particular design narrative. If you are familiar with higher normal forms
beyond 1NF, every principled decomposition to avoid moification anomalies is
definitely worth the "trouble".
BTW, NULLs have nothing to do with any normal forms beyond 1NF.
>> It seems like it would be alot easier for INSERTS, UPDATES, and SELECTS
>> to have the extra title fields as part of the Products database and just
>> store empty strings in them if I don't have a value.
"Ease" of writing INSERTS, UPDATES, and SELECTS is not a design principle;
but integrity preservation is. You wouldn't consider a single table for
representing the entire schema by such an assessment of ease, would you?
>> Just want to confirm that I'm heading in the right direction.
If you are considering full normalization in your logical design process,
you are on the right track.
--
Anith|||I agree with the assessment that no sound judgement may be passed on any
particular design without the full business rules the design was based on;
however, there are a few problems that crop up now and again.
First, database normalization is a classification mechanism that attempts to
model a real world system. It is a method of logically seperating out
individually defined "nouns" or entities.
What I often see is that developers new to database design tend to apply
leasons learned from OOP/OOA to data modeling. Database design is a
reductionist exercise to classify and reduce data redundancy. OOP/OOA is an
aggregation of process consolidation whereby a user, by generalization, may
abstract out common features. These are at two polar extremes, or orthogonal
to one another. Be careful.
The first sign that you may have strayed too far is when you start building
MUCK tables and overly use Many-to-Many relationships. Everything is a
"type" with an ID, Name, and description, right? WRONG! Don't go down that
track.
Sincerely,
Anthony Thomas
"Anith Sen" wrote:
> >> How far should I go with normalizing my database?
> As far as you can. In other words, full normalization upto BCNF in case of
> tables with single column keys ( which will be in 5NF anyway ) and upto 5NF
> in case of tables with composite keys and overlapping sets of values in
> different rows are mandatory to avoid data modification anomalies.
> >> Is this the correct way to design the database? Is it worth the trouble
> >> to do it this way?
> Without a detailed knowledge of your business model and existing entity
> types, its attributes and applicable relationships others cannot comment on
> a particular design narrative. If you are familiar with higher normal forms
> beyond 1NF, every principled decomposition to avoid moification anomalies is
> definitely worth the "trouble".
> BTW, NULLs have nothing to do with any normal forms beyond 1NF.
> >> It seems like it would be alot easier for INSERTS, UPDATES, and SELECTS
> >> to have the extra title fields as part of the Products database and just
> >> store empty strings in them if I don't have a value.
> "Ease" of writing INSERTS, UPDATES, and SELECTS is not a design principle;
> but integrity preservation is. You wouldn't consider a single table for
> representing the entire schema by such an assessment of ease, would you?
> >> Just want to confirm that I'm heading in the right direction.
> If you are considering full normalization in your logical design process,
> you are on the right track.
> --
> Anith
>
>|||Perhaps Anith is more meticulous than I , but I generally go to 3rd Normal
Form, and call it a day...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jason Hedges" <jasonh@.bsmgr.com> wrote in message
news:557901c4c6b0$721c0840$a301280a@.phx.gbl...
> How far should I go with normalizing my database? I'm
> designing a database for products (books, music, videos,
> etc.). I'm also designing a .Net front-end app for
> managing the products in the SQL database. My frustration
> is having to take the normalized data and convert it back
> to a usable form for the management screens and then
> saving it back to the normalized database. Here's an
> example of the design:
> I created a table named "Products" as a master table for
> product information: Products(ItemNumber, Title, Retail)
> I created a table named "TitleTypes" that holds a list of
> different types of titles (Sub-Title, Foreign Title, Misc
> Title, Exact Title, etc.): TitleTypes(TypeID, Description)
> I created a table named "Products_AdditionalTitles" to
> hold a list of additional titles for a given product and
> what type of title it is. This table is related to the
> two tables above based on the ItemNumber and TypeID
> fields: Products_AdditionalTitles(ItemNumber,TypeID,Title)
> Some books have sub-titles, foreign titles, etc. and some
> don't so this allows each book to have an many or as few
> titles as needed without storing NULLS or empty strings
> in the database for books that don't have values for
> these fields.
> Is this the correct way to design the database? Is it
> worth the trouble to do it this way? It seems like it
> would be alot easier for INSERTS, UPDATES, and SELECTS to
> have the extra title fields as part of the Products
> database and just store empty strings in them if I don't
> have a value.
> Just want to confirm that I'm heading in the right
> direction.
> Thanks!|||Ok, maybe I've travelled too far down the "everything is
a type" road already.
Here's the scenario: I am designing a new database and
application in SQL Server and .Net for managing product
information for bookstores (strictly product data, not
sales, customers, etc.). The current database and
application have been developed with COBOL. The main
types of products we deal with are books, music, bibles,
videos, software, and gifts. Every product has some
common attributes (item number, title, retail price,
vendor) and some products have additional attributes (sub-
title, measurements, publish date, release date, foreign
language title, etc.). Products can have from 1 to 3
contributors (author, artist) and be categorized in 1 to
3 categories.
I've ended up with alot of many-to-many tables. For
example, I have the main products tables, a master
category table, and a many-to-many table that links
products with multiple categories. Same thing for
contributors. Another example: since there are several
types of dates that can be stored for a product (publish
date, release date, etc.), I have a "Date Type" table and
a many-to-many table that holds a list of dates and their
respective "date type" and the product item number they
belong to. I thought this would be the best way since
the number of each was an unknown.
I was under the impression that having a lot of fields in
a table that were not used was poor design. Example:
having a SubTitle field in my main product database would
be poor design since many products don't have a sub-
title. Is that true or false?
I'd appreciate some more input if you have enough info
here to go on.
Thanks!
Jason
>--Original Message--
>I agree with the assessment that no sound judgement may
be passed on any
>particular design without the full business rules the
design was based on;
>however, there are a few problems that crop up now and
again.
>First, database normalization is a classification
mechanism that attempts to
>model a real world system. It is a method of logically
seperating out
>individually defined "nouns" or entities.
>What I often see is that developers new to database
design tend to apply
>leasons learned from OOP/OOA to data modeling. Database
design is a
>reductionist exercise to classify and reduce data
redundancy. OOP/OOA is an
>aggregation of process consolidation whereby a user, by
generalization, may
>abstract out common features. These are at two polar
extremes, or orthogonal
>to one another. Be careful.
>The first sign that you may have strayed too far is when
you start building
>MUCK tables and overly use Many-to-Many relationships.
Everything is a
>"type" with an ID, Name, and description, right?
WRONG! Don't go down that
>track.
>Sincerely,
>
>Anthony Thomas
>
>"Anith Sen" wrote:
>> >> How far should I go with normalizing my database?
>> As far as you can. In other words, full normalization
upto BCNF in case of
>> tables with single column keys ( which will be in 5NF
anyway ) and upto 5NF
>> in case of tables with composite keys and overlapping
sets of values in
>> different rows are mandatory to avoid data
modification anomalies.
>> >> Is this the correct way to design the database? Is
it worth the trouble
>> >> to do it this way?
>> Without a detailed knowledge of your business model
and existing entity
>> types, its attributes and applicable relationships
others cannot comment on
>> a particular design narrative. If you are familiar
with higher normal forms
>> beyond 1NF, every principled decomposition to avoid
moification anomalies is
>> definitely worth the "trouble".
>> BTW, NULLs have nothing to do with any normal forms
beyond 1NF.
>> >> It seems like it would be alot easier for INSERTS,
UPDATES, and SELECTS
>> >> to have the extra title fields as part of the
Products database and just
>> >> store empty strings in them if I don't have a value.
>> "Ease" of writing INSERTS, UPDATES, and SELECTS is not
a design principle;
>> but integrity preservation is. You wouldn't consider a
single table for
>> representing the entire schema by such an assessment
of ease, would you?
>> >> Just want to confirm that I'm heading in the right
direction.
>> If you are considering full normalization in your
logical design process,
>> you are on the right track.
>> --
>> Anith
>>
>.
>|||"Jason Hedges" <jasonh@.bsmgr.com> wrote in message
news:505a01c4c737$16d9aeb0$a601280a@.phx.gbl...
I'm very interested in this discussion, as I recently completed a
bibliography project that is somewhat similar to your bookstore project (see
http://mwilden.com/forester/).
I always start from the user interface. What does the user want to do on a
screen, or with a report. What buttons does he want to push? What rows and
columns of stuff does he want to see? A database is just a tool to achieve
that.
Note that this is quite different from saying that a database should model
or mirror a UI. But all database decisions must eventually come down to "how
does this help the user?".
What would be interesting is to know what your users expect from this
application. For example, do they need to search for subtitles? If so,
subtitle should clearly be a separate column (possibly in a separate table).
On the other hand, if they don't need to search, or otherwise need to
distinguish among, subtitles, why make it a separate column at all? Why not
just tack it on to the end of the title, as in modern library practice?
I'm not suggesting one or the other - just that you have to first find out
what the users expect from your system. There's no way to design a
"bookstore database" without considering such things. And normalization
(contrary to popular belief) must also be driven by these concerns. In one
database, normalization would require storing an address separately from its
city. In another, it would require storing only a latitude and longitude,
which would be related to a table of addresses. It depends on the
application.
> Every product has some
> common attributes (item number, title, retail price,
> vendor) and some products have additional attributes (sub-
> title, measurements, publish date, release date, foreign
> language title, etc.).
All products have measurements in the real world. Whether you want to record
them for all products depends on the user requirements. Similarly with all
the other characteristics you mention.
> Products can have from 1 to 3
> contributors (author, artist) and be categorized in 1 to
> 3 categories.
This may match your user's needs, but probably not. Lots of books have more
than three authors, to say nothing of editors, illustrators, dustjacket
artists, designers, etc.
> I have a "Date Type" table and
> a many-to-many table that holds a list of dates and their
> respective "date type" and the product item number they
> belong to. I thought this would be the best way since
> the number of each was an unknown.
However, the upper bound of such is known. Every book does indeed have a
first French publication date - in most cases, however, that value is NULL.
I don't see a problem with modelling that fact in a column instead of a
relationship. The thing is, you're going to have to LEFT JOIN your m-to-m
table to get back the NULL at some point anyway.
On the other hand, if you want to model editions specifically (as I did in
my C.S. Forester database), you won't want to store the French publication
date in one table and the French title in another - you'll need an Editions
table that records this information (and probably more) in one place. It
depends on what the users want.
> I was under the impression that having a lot of fields in
> a table that were not used was poor design. Example:
> having a SubTitle field in my main product database would
> be poor design since many products don't have a sub-
> title. Is that true or false?
Again, it depends how you want to use the subtitle concept. Odds are (I'm
guessing), you want to display the subtitle of every book at some point.
Instead of linking to a table to do this, why not just display its column
(even if it's NULL)? (A variable-length column takes almost no space if it's
NULL.)|||Mark is on a different path, although, headed in the right direction.
First, you need to understand this, the goals of database design and those
of application design are predicated on two different, sometimes opposing,
sometimes cooperative, criteria. Applications are designed for data
MANIPULATION and PROCESS. Databases, however, are designed for data
INTEGRITY and IDENTIFICATION.
In the sense that you must understand what the User wants is absolutely
correct. For the application, the GUI and the process of information is a
perfectly acceptable viewpoint to model the appliction design and process.
Howerver, this is completely wrong for the design of the database system.
I am assuming that many of those "unique" attributes are based on the
product "type." If there are truely common caracteristics of Entity Classes,
then perhaps a Super Table Subordinate Table relation would work better,
although the current SQL DBMSes do not fully support this sort of structure
well.
The idea is that there are Products, which have common attributes, but,
then, there are Books that are dissimilar from software and videos. Now, you
can call all of these Products, but, then, we must deal with the type
specific attributes. All in the same table allowed NULL? Seperate table and
make entries for only those that have data? For all of the unique columns or
only some because it is likely that you will only populate some base on type.
Aha, sounds like a subordinate class. You create a Book table for the Book
entity, a Video table for the Video entity, etc, each with its own specific
attributes. You can tie all of these back to a common Products table to
carry the common attributes if you wish. The point is that Books are not
Videos are not Software, even if they do have common characteristics. Just
because humans and fish have eyes and mouths does not make a human a fish nor
a fish a human. Aha, but they are both animals. See the structure?
The point is that data modeling comes down to describing the real world, not
as a process, but as a classification, for delineating, reducing, individual
attributes.
I think you basically had the right ideas for most of what you described;
however, take a look at the seperate or super-sub table structure. But the
dates, no, that is where you started getting into the MUCK area. A
pulication date is not a release date any more than a start time is the same
as an end time. Is your birthdate the same as your death date? I certainly
hope not!
Forget about all of that garbage about normalization performance. Research
has shown that the proper normalization of data to ensure the integrity and
reduce the duplicity of data is by far the best means to garauntee the best
performance. That is why we have DBMSes, to maximize the performance GIVEN a
normalized database. A DBMS is a physical mechanism, the data model and its
normalization is a logical one. The two are indepenent, although related, or
better, derived. That is, the physical is derived from the logical, not the
other way around. So, performance should never be a constraint on the
design, only the integrity of the system.
Feel free to follow up if you think we could provide you with any further
assistance.
Check out "An Introduction to Database Systems" by C. J. Date. I think you
would find it enlightening.
Sincerely,
Anthony Thomas
"Jason Hedges" wrote:
> Ok, maybe I've travelled too far down the "everything is
> a type" road already.
> Here's the scenario: I am designing a new database and
> application in SQL Server and .Net for managing product
> information for bookstores (strictly product data, not
> sales, customers, etc.). The current database and
> application have been developed with COBOL. The main
> types of products we deal with are books, music, bibles,
> videos, software, and gifts. Every product has some
> common attributes (item number, title, retail price,
> vendor) and some products have additional attributes (sub-
> title, measurements, publish date, release date, foreign
> language title, etc.). Products can have from 1 to 3
> contributors (author, artist) and be categorized in 1 to
> 3 categories.
> I've ended up with alot of many-to-many tables. For
> example, I have the main products tables, a master
> category table, and a many-to-many table that links
> products with multiple categories. Same thing for
> contributors. Another example: since there are several
> types of dates that can be stored for a product (publish
> date, release date, etc.), I have a "Date Type" table and
> a many-to-many table that holds a list of dates and their
> respective "date type" and the product item number they
> belong to. I thought this would be the best way since
> the number of each was an unknown.
> I was under the impression that having a lot of fields in
> a table that were not used was poor design. Example:
> having a SubTitle field in my main product database would
> be poor design since many products don't have a sub-
> title. Is that true or false?
> I'd appreciate some more input if you have enough info
> here to go on.
> Thanks!
> Jason
> >--Original Message--
> >I agree with the assessment that no sound judgement may
> be passed on any
> >particular design without the full business rules the
> design was based on;
> >however, there are a few problems that crop up now and
> again.
> >
> >First, database normalization is a classification
> mechanism that attempts to
> >model a real world system. It is a method of logically
> seperating out
> >individually defined "nouns" or entities.
> >
> >What I often see is that developers new to database
> design tend to apply
> >leasons learned from OOP/OOA to data modeling. Database
> design is a
> >reductionist exercise to classify and reduce data
> redundancy. OOP/OOA is an
> >aggregation of process consolidation whereby a user, by
> generalization, may
> >abstract out common features. These are at two polar
> extremes, or orthogonal
> >to one another. Be careful.
> >
> >The first sign that you may have strayed too far is when
> you start building
> >MUCK tables and overly use Many-to-Many relationships.
> Everything is a
> >"type" with an ID, Name, and description, right?
> WRONG! Don't go down that
> >track.
> >
> >Sincerely,
> >
> >
> >Anthony Thomas
> >
> >
> >"Anith Sen" wrote:
> >
> >> >> How far should I go with normalizing my database?
> >>
> >> As far as you can. In other words, full normalization
> upto BCNF in case of
> >> tables with single column keys ( which will be in 5NF
> anyway ) and upto 5NF
> >> in case of tables with composite keys and overlapping
> sets of values in
> >> different rows are mandatory to avoid data
> modification anomalies.
> >>
> >> >> Is this the correct way to design the database? Is
> it worth the trouble
> >> >> to do it this way?
> >>
> >> Without a detailed knowledge of your business model
> and existing entity
> >> types, its attributes and applicable relationships
> others cannot comment on
> >> a particular design narrative. If you are familiar
> with higher normal forms
> >> beyond 1NF, every principled decomposition to avoid
> moification anomalies is
> >> definitely worth the "trouble".
> >>
> >> BTW, NULLs have nothing to do with any normal forms
> beyond 1NF.
> >>
> >> >> It seems like it would be alot easier for INSERTS,
> UPDATES, and SELECTS
> >> >> to have the extra title fields as part of the
> Products database and just
> >> >> store empty strings in them if I don't have a value.
> >>
> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is not
> a design principle;
> >> but integrity preservation is. You wouldn't consider a
> single table for
> >> representing the entire schema by such an assessment
> of ease, would you?
> >>
> >> >> Just want to confirm that I'm heading in the right
> direction.
> >>
> >> If you are considering full normalization in your
> logical design process,
> >> you are on the right track.
> >>
> >> --
> >> Anith
> >>
> >>
> >>
> >.
> >
>|||Anthony,
Thanks for your reply and the book recommendation.
Can you give me more direction on the date issue? I
understand the logic behind the books, videos, software,
etc. What do you do with data like the date fields?
Should each date type (pub date, street date, etc.) be a
separate field/column in a table? I thought the way I had
done it allowed for alot of flexibility because you
didn't have to change the database design to store new
types of data that were similar to other types already
being stored. Here's another example (similar to the date
scenario):
I have a table that defines "title types" (sub title,
foreign title, title as it appears on the product, etc.).
I have a many-to-many relationship table that stores a
product identifier (relating back to the master product
table), a title, and a title type identifer (relating to
the title types table). This seemed flexible since I can
decide to store a "misc title" simply by adding
another "title type" to my title types table and using
it's identifer.
How should I be storing this type of data? Should each
type of title be a separate field in a table? Should I
just store NULLS or empty strings when I don't have a
value for one of these fields?
Thanks!
Jason
>--Original Message--
>Mark is on a different path, although, headed in the
right direction.
>First, you need to understand this, the goals of
database design and those
>of application design are predicated on two different,
sometimes opposing,
>sometimes cooperative, criteria. Applications are
designed for data
>MANIPULATION and PROCESS. Databases, however, are
designed for data
>INTEGRITY and IDENTIFICATION.
>In the sense that you must understand what the User
wants is absolutely
>correct. For the application, the GUI and the process
of information is a
>perfectly acceptable viewpoint to model the appliction
design and process.
>Howerver, this is completely wrong for the design of the
database system.
>I am assuming that many of those "unique" attributes are
based on the
>product "type." If there are truely common
caracteristics of Entity Classes,
>then perhaps a Super Table Subordinate Table relation
would work better,
>although the current SQL DBMSes do not fully support
this sort of structure
>well.
>The idea is that there are Products, which have common
attributes, but,
>then, there are Books that are dissimilar from software
and videos. Now, you
>can call all of these Products, but, then, we must deal
with the type
>specific attributes. All in the same table allowed
NULL? Seperate table and
>make entries for only those that have data? For all of
the unique columns or
>only some because it is likely that you will only
populate some base on type.
> Aha, sounds like a subordinate class. You create a
Book table for the Book
>entity, a Video table for the Video entity, etc, each
with its own specific
>attributes. You can tie all of these back to a common
Products table to
>carry the common attributes if you wish. The point is
that Books are not
>Videos are not Software, even if they do have common
characteristics. Just
>because humans and fish have eyes and mouths does not
make a human a fish nor
>a fish a human. Aha, but they are both animals. See
the structure?
>The point is that data modeling comes down to describing
the real world, not
>as a process, but as a classification, for delineating,
reducing, individual
>attributes.
>I think you basically had the right ideas for most of
what you described;
>however, take a look at the seperate or super-sub table
structure. But the
>dates, no, that is where you started getting into the
MUCK area. A
>pulication date is not a release date any more than a
start time is the same
>as an end time. Is your birthdate the same as your
death date? I certainly
>hope not!
>Forget about all of that garbage about normalization
performance. Research
>has shown that the proper normalization of data to
ensure the integrity and
>reduce the duplicity of data is by far the best means to
garauntee the best
>performance. That is why we have DBMSes, to maximize
the performance GIVEN a
>normalized database. A DBMS is a physical mechanism,
the data model and its
>normalization is a logical one. The two are indepenent,
although related, or
>better, derived. That is, the physical is derived from
the logical, not the
>other way around. So, performance should never be a
constraint on the
>design, only the integrity of the system.
>Feel free to follow up if you think we could provide you
with any further
>assistance.
>Check out "An Introduction to Database Systems" by C. J.
Date. I think you
>would find it enlightening.
>Sincerely,
>
>Anthony Thomas
>
>"Jason Hedges" wrote:
>> Ok, maybe I've travelled too far down the "everything
is
>> a type" road already.
>> Here's the scenario: I am designing a new database and
>> application in SQL Server and .Net for managing
product
>> information for bookstores (strictly product data, not
>> sales, customers, etc.). The current database and
>> application have been developed with COBOL. The main
>> types of products we deal with are books, music,
bibles,
>> videos, software, and gifts. Every product has some
>> common attributes (item number, title, retail price,
>> vendor) and some products have additional attributes
(sub-
>> title, measurements, publish date, release date,
foreign
>> language title, etc.). Products can have from 1 to 3
>> contributors (author, artist) and be categorized in 1
to
>> 3 categories.
>> I've ended up with alot of many-to-many tables. For
>> example, I have the main products tables, a master
>> category table, and a many-to-many table that links
>> products with multiple categories. Same thing for
>> contributors. Another example: since there are several
>> types of dates that can be stored for a product
(publish
>> date, release date, etc.), I have a "Date Type" table
and
>> a many-to-many table that holds a list of dates and
their
>> respective "date type" and the product item number
they
>> belong to. I thought this would be the best way since
>> the number of each was an unknown.
>> I was under the impression that having a lot of fields
in
>> a table that were not used was poor design. Example:
>> having a SubTitle field in my main product database
would
>> be poor design since many products don't have a sub-
>> title. Is that true or false?
>> I'd appreciate some more input if you have enough info
>> here to go on.
>> Thanks!
>> Jason
>> >--Original Message--
>> >I agree with the assessment that no sound judgement
may
>> be passed on any
>> >particular design without the full business rules the
>> design was based on;
>> >however, there are a few problems that crop up now
and
>> again.
>> >
>> >First, database normalization is a classification
>> mechanism that attempts to
>> >model a real world system. It is a method of
logically
>> seperating out
>> >individually defined "nouns" or entities.
>> >
>> >What I often see is that developers new to database
>> design tend to apply
>> >leasons learned from OOP/OOA to data modeling.
Database
>> design is a
>> >reductionist exercise to classify and reduce data
>> redundancy. OOP/OOA is an
>> >aggregation of process consolidation whereby a user,
by
>> generalization, may
>> >abstract out common features. These are at two polar
>> extremes, or orthogonal
>> >to one another. Be careful.
>> >
>> >The first sign that you may have strayed too far is
when
>> you start building
>> >MUCK tables and overly use Many-to-Many
relationships.
>> Everything is a
>> >"type" with an ID, Name, and description, right?
>> WRONG! Don't go down that
>> >track.
>> >
>> >Sincerely,
>> >
>> >
>> >Anthony Thomas
>> >
>> >
>> >"Anith Sen" wrote:
>> >
>> >> >> How far should I go with normalizing my database?
>> >>
>> >> As far as you can. In other words, full
normalization
>> upto BCNF in case of
>> >> tables with single column keys ( which will be in
5NF
>> anyway ) and upto 5NF
>> >> in case of tables with composite keys and
overlapping
>> sets of values in
>> >> different rows are mandatory to avoid data
>> modification anomalies.
>> >>
>> >> >> Is this the correct way to design the database?
Is
>> it worth the trouble
>> >> >> to do it this way?
>> >>
>> >> Without a detailed knowledge of your business model
>> and existing entity
>> >> types, its attributes and applicable relationships
>> others cannot comment on
>> >> a particular design narrative. If you are familiar
>> with higher normal forms
>> >> beyond 1NF, every principled decomposition to avoid
>> moification anomalies is
>> >> definitely worth the "trouble".
>> >>
>> >> BTW, NULLs have nothing to do with any normal forms
>> beyond 1NF.
>> >>
>> >> >> It seems like it would be alot easier for
INSERTS,
>> UPDATES, and SELECTS
>> >> >> to have the extra title fields as part of the
>> Products database and just
>> >> >> store empty strings in them if I don't have a
value.
>> >>
>> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is
not
>> a design principle;
>> >> but integrity preservation is. You wouldn't
consider a
>> single table for
>> >> representing the entire schema by such an
assessment
>> of ease, would you?
>> >>
>> >> >> Just want to confirm that I'm heading in the
right
>> direction.
>> >>
>> >> If you are considering full normalization in your
>> logical design process,
>> >> you are on the right track.
>> >>
>> >> --
>> >> Anith
>> >>
>> >>
>> >>
>> >.
>> >
>.
>|||Like Mark replied, it depends on the requirements. In that, I agree;
however, where our friend says he starts with User Interface and the process,
this is where I disagree. Although it does DEPEND, data models depend on ONE
thing, the BUSINESS MODEL, which should have reflacted the real world
structure.
You have asked two seperate questions here. So, let's start with the easier
one, the Titles. First of all, this is not a Many-to-Many relationship; it
is a One-to-Many-to-One relationship: Products, Other Titles, Title Types.
I would claim that the Title is an attribute of the Product; moreover, it is
a candidate key. I would require each of my products to have a title. How
else would you define it? Now, some products may have 1 or more Other
Titles, or Additional Titles. This sentence clues you into what kind of
structure this should have. "Some Products" implies a 0, 1, or N relation.
If it were simply 0 or 1, then a NULL attribute may be sufficient.
I tend shy away from NULL attributes and use subsidiary tables unless the
creation and management of that additional table would be overkill with
respect to the original attribute. My favorite example is the Middle Initial
attribute. Now, in this case, seperating that attribute out to a dependent
subsidiary table would be overkill.
In your case, however, this is what we will need. Why? Because, in all
likelihood, the potential table could be a very large attribute and may not
be queried very frequently; so, why embed such a thing in the original table?
In addition, you will want to potentially have Many additional or subsidiary
titles. This requires the seperation.
This table would be One or Many-to-One against the Product table. Then, you
will probably want to classify the titles based on type, restricted or
otherwise, in the sense that you may allow only one additional title per
type. This would spawn the process of the type table that would be in
One-to-Many correspondence with the Other Titles table.
Let me know if that does not make sense.
Now, the dates. Here is a reason to NOT split this out to a Many-to-Many
relationship: how would you query it? Many-to-Many relationships have a
tendancy, especially the "Type" table kinds, to turn columns (attribute
names) in to data rows. When that happens, you have turned a simple SELECT
column FROM table1 (or joined to table2) from a horridous WHERE clause where
you have to specify multiple filter commands just to discover the record you
are after BEFORE you can determine the "VALUE" column that contains the data.
I've seen this numerous times and it will kill your system.
Now, I do not want to backtrack by saying that Performance should overrule
design. NO. However, oftentimes bad design will kill performance, no matter
what you do to improve it. Proper design, even when it looks like a
performance killer at first (usually because of the induced additional joins)
will usually save your performance in the long run.
Here is what I mean. A table is a representation of an Entity--a thing, a
noun, a specific item--that "relates" all of the appropriate and dependent
characteristics of that Entity. Now, your products have dates.
Normalization would dictate that these dates would depend on the KEY, the
WHOLE KEY, and NOTHING BUT THE KEY. I will assume that your products have a
primary TITLE (?) as the identifying characteristic. Perhaps it is some sort
of combination, regardless if you have introduced some sort of machine
generated ID surrogate key.
Now, the question is: Are these Dates Entities on their own, or, are they
somehow dependent on the Product Entity? I can see at least two ways this
may be true, although there is surely others.
1. The dates are individually unique attributes. The misnaming of them as
Date1, Date2, etc. may cause someone to believe the table has now violated
1NF or 2NF; however, if those attributes are not generic and follow the KEY
rules, then they are perfectly acceptable as embedded attributes within the
Entity they are related to.
2. There is some form of functional or constrained dependancy between the
dates. This would indicate the prescence and need for higher forms of
normalization: Boyce-Codd NF, 4NF, 5NF, or the new 6NF. These may not be
collection of dates but Product Release Schedules, a different but related
Entity, assigned (or related) to the Product Entity, which have ScheduleDate
as one of their attributes.
Regardless, you will have to decide the classification of these but DO NOT
GENERICIZE the attributes into a Muck Table: ID, Name, Description with the
associated crosstab table with the Value attribute. Type tables are
necessary but not all inclusive. The Value attribute has little value and
should be an indicator that the Entity you have defined has been genericized.
An Entity only exists if it is well-defined and models a real world element,
not an abstracted OO class.
I hope this makes sense. Feel free to continue the conversation if it does
not.
Sincerely,
Anthony Thomas
"Jason Hedges" wrote:
> Anthony,
> Thanks for your reply and the book recommendation.
> Can you give me more direction on the date issue? I
> understand the logic behind the books, videos, software,
> etc. What do you do with data like the date fields?
> Should each date type (pub date, street date, etc.) be a
> separate field/column in a table? I thought the way I had
> done it allowed for alot of flexibility because you
> didn't have to change the database design to store new
> types of data that were similar to other types already
> being stored. Here's another example (similar to the date
> scenario):
> I have a table that defines "title types" (sub title,
> foreign title, title as it appears on the product, etc.).
> I have a many-to-many relationship table that stores a
> product identifier (relating back to the master product
> table), a title, and a title type identifer (relating to
> the title types table). This seemed flexible since I can
> decide to store a "misc title" simply by adding
> another "title type" to my title types table and using
> it's identifer.
> How should I be storing this type of data? Should each
> type of title be a separate field in a table? Should I
> just store NULLS or empty strings when I don't have a
> value for one of these fields?
> Thanks!
> Jason
> >--Original Message--
> >Mark is on a different path, although, headed in the
> right direction.
> >
> >First, you need to understand this, the goals of
> database design and those
> >of application design are predicated on two different,
> sometimes opposing,
> >sometimes cooperative, criteria. Applications are
> designed for data
> >MANIPULATION and PROCESS. Databases, however, are
> designed for data
> >INTEGRITY and IDENTIFICATION.
> >
> >In the sense that you must understand what the User
> wants is absolutely
> >correct. For the application, the GUI and the process
> of information is a
> >perfectly acceptable viewpoint to model the appliction
> design and process.
> >Howerver, this is completely wrong for the design of the
> database system.
> >
> >I am assuming that many of those "unique" attributes are
> based on the
> >product "type." If there are truely common
> caracteristics of Entity Classes,
> >then perhaps a Super Table Subordinate Table relation
> would work better,
> >although the current SQL DBMSes do not fully support
> this sort of structure
> >well.
> >
> >The idea is that there are Products, which have common
> attributes, but,
> >then, there are Books that are dissimilar from software
> and videos. Now, you
> >can call all of these Products, but, then, we must deal
> with the type
> >specific attributes. All in the same table allowed
> NULL? Seperate table and
> >make entries for only those that have data? For all of
> the unique columns or
> >only some because it is likely that you will only
> populate some base on type.
> > Aha, sounds like a subordinate class. You create a
> Book table for the Book
> >entity, a Video table for the Video entity, etc, each
> with its own specific
> >attributes. You can tie all of these back to a common
> Products table to
> >carry the common attributes if you wish. The point is
> that Books are not
> >Videos are not Software, even if they do have common
> characteristics. Just
> >because humans and fish have eyes and mouths does not
> make a human a fish nor
> >a fish a human. Aha, but they are both animals. See
> the structure?
> >
> >The point is that data modeling comes down to describing
> the real world, not
> >as a process, but as a classification, for delineating,
> reducing, individual
> >attributes.
> >
> >I think you basically had the right ideas for most of
> what you described;
> >however, take a look at the seperate or super-sub table
> structure. But the
> >dates, no, that is where you started getting into the
> MUCK area. A
> >pulication date is not a release date any more than a
> start time is the same
> >as an end time. Is your birthdate the same as your
> death date? I certainly
> >hope not!
> >
> >Forget about all of that garbage about normalization
> performance. Research
> >has shown that the proper normalization of data to
> ensure the integrity and
> >reduce the duplicity of data is by far the best means to
> garauntee the best
> >performance. That is why we have DBMSes, to maximize
> the performance GIVEN a
> >normalized database. A DBMS is a physical mechanism,
> the data model and its
> >normalization is a logical one. The two are indepenent,
> although related, or
> >better, derived. That is, the physical is derived from
> the logical, not the
> >other way around. So, performance should never be a
> constraint on the
> >design, only the integrity of the system.
> >
> >Feel free to follow up if you think we could provide you
> with any further
> >assistance.
> >
> >Check out "An Introduction to Database Systems" by C. J.
> Date. I think you
> >would find it enlightening.
> >
> >Sincerely,
> >
> >
> >Anthony Thomas
> >
> >
> >"Jason Hedges" wrote:
> >
> >>
> >> Ok, maybe I've travelled too far down the "everything
> is
> >> a type" road already.
> >>
> >> Here's the scenario: I am designing a new database and
> >> application in SQL Server and .Net for managing
> product
> >> information for bookstores (strictly product data, not
> >> sales, customers, etc.). The current database and
> >> application have been developed with COBOL. The main
> >> types of products we deal with are books, music,
> bibles,
> >> videos, software, and gifts. Every product has some
> >> common attributes (item number, title, retail price,
> >> vendor) and some products have additional attributes
> (sub-
> >> title, measurements, publish date, release date,
> foreign
> >> language title, etc.). Products can have from 1 to 3
> >> contributors (author, artist) and be categorized in 1
> to
> >> 3 categories.
> >>
> >> I've ended up with alot of many-to-many tables. For
> >> example, I have the main products tables, a master
> >> category table, and a many-to-many table that links
> >> products with multiple categories. Same thing for
> >> contributors. Another example: since there are several
> >> types of dates that can be stored for a product
> (publish
> >> date, release date, etc.), I have a "Date Type" table
> and
> >> a many-to-many table that holds a list of dates and
> their
> >> respective "date type" and the product item number
> they
> >> belong to. I thought this would be the best way since
> >> the number of each was an unknown.
> >>
> >> I was under the impression that having a lot of fields
> in
> >> a table that were not used was poor design. Example:
> >> having a SubTitle field in my main product database
> would
> >> be poor design since many products don't have a sub-
> >> title. Is that true or false?
> >>
> >> I'd appreciate some more input if you have enough info
> >> here to go on.
> >>
> >> Thanks!
> >> Jason
> >>
> >> >--Original Message--
> >> >I agree with the assessment that no sound judgement
> may
> >> be passed on any
> >> >particular design without the full business rules the
> >> design was based on;
> >> >however, there are a few problems that crop up now
> and
> >> again.
> >> >
> >> >First, database normalization is a classification
> >> mechanism that attempts to
> >> >model a real world system. It is a method of
> logically
> >> seperating out
> >> >individually defined "nouns" or entities.
> >> >
> >> >What I often see is that developers new to database
> >> design tend to apply
> >> >leasons learned from OOP/OOA to data modeling.
> Database
> >> design is a
> >> >reductionist exercise to classify and reduce data
> >> redundancy. OOP/OOA is an
> >> >aggregation of process consolidation whereby a user,
> by
> >> generalization, may
> >> >abstract out common features. These are at two polar
> >> extremes, or orthogonal
> >> >to one another. Be careful.
> >> >
> >> >The first sign that you may have strayed too far is
> when
> >> you start building
> >> >MUCK tables and overly use Many-to-Many
> relationships.
> >> Everything is a
> >> >"type" with an ID, Name, and description, right?
> >> WRONG! Don't go down that
> >> >track.
> >> >
> >> >Sincerely,
> >> >
> >> >
> >> >Anthony Thomas
> >> >
> >> >
> >> >"Anith Sen" wrote:
> >> >
> >> >> >> How far should I go with normalizing my database?
> >> >>
> >> >> As far as you can. In other words, full
> normalization
> >> upto BCNF in case of
> >> >> tables with single column keys ( which will be in
> 5NF
> >> anyway ) and upto 5NF
> >> >> in case of tables with composite keys and
> overlapping
> >> sets of values in
> >> >> different rows are mandatory to avoid data
> >> modification anomalies.
> >> >>
> >> >> >> Is this the correct way to design the database?
> Is
> >> it worth the trouble
> >> >> >> to do it this way?
> >> >>
> >> >> Without a detailed knowledge of your business model
> >> and existing entity
> >> >> types, its attributes and applicable relationships
> >> others cannot comment on
> >> >> a particular design narrative. If you are familiar
> >> with higher normal forms
> >> >> beyond 1NF, every principled decomposition to avoid
> >> moification anomalies is
> >> >> definitely worth the "trouble".
> >> >>
> >> >> BTW, NULLs have nothing to do with any normal forms
> >> beyond 1NF.
> >> >>
> >> >> >> It seems like it would be alot easier for
> INSERTS,
> >> UPDATES, and SELECTS
> >> >> >> to have the extra title fields as part of the
> >> Products database and just
> >> >> >> store empty strings in them if I don't have a
> value.
> >> >>
> >> >> "Ease" of writing INSERTS, UPDATES, and SELECTS is
> not
> >> a design principle;
> >> >> but integrity preservation is. You wouldn't
> consider a
> >> single table for
> >> >> representing the entire schema by such an
> assessment
> >> of ease, would you?
> >> >>
> >> >> >> Just want to confirm that I'm heading in the
> right
> >> direction.
> >> >>
> >> >> If you are considering full normalization in your
> >> logical design process,
> >> >> you are on the right track.
> >> >>
> >> >> --
> >> >> Anith
> >> >>
> >> >>
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>|||"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:AD17439F-180F-49E2-BB21-1DECA363C834@.microsoft.com...
> Like Mark replied, it depends on the requirements. In that, I agree;
> however, where our friend says he starts with User Interface and the
process,
> this is where I disagree.
Just to be clear, I mean that the user requirements drive the database, not
the "real world." The real world is too big to model. The hard part (that I
think our other friend needs to better define) is what part of that real
world is important to his users. Since all the users care about is what they
see on the screen or on a bit of paper, starting from the user interface is
usually the best way to determine what part of the real world needs to be
modelled.
Take subtitles, for example. If the user requires a screen that lets him
search for subtitles, then clearly, subtitles need to be in a column of
their own. If such a search (= screen) isn't required, subtitles could
simply be included in the title. The real world (alone) doesn't allow one to
make this choice.
> I would claim that the Title is an attribute of the Product; moreover, it
is
> a candidate key.
Actually, titles aren't unique (in the real world:).
> My favorite example is the Middle Initial
> attribute. Now, in this case, seperating that attribute out to a
dependent
> subsidiary table would be overkill.
But in "the real world," middle initial is a separate piece of information
from the rest of the name. What defines "overkill" is not the real world,
but the user requirements.
I do agree with most of your points, however.|||Then, perhaps, we agree more than what we've originally indicated. And, to
our friend with the problem, this isn't easy.
The point is that it is impossible for anyone out here to give you specific
design criteria. For that, the Database Engineer MUST BE very close the
end-user requirements.
Lastly, all I would say to the "real world" model--a rather too loose term,
I agree--is that data modeling is more an intent of data definition; whereas,
application design is more intent on process modling. At some point, the two
will need to work together, but it must be recognized that the design goals
of each are targeted at two different aspects of the same project.
Oftentimes, these targets are divergent.
Sincerely,
Anthony Thomas
"Mark Wilden" wrote:
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:AD17439F-180F-49E2-BB21-1DECA363C834@.microsoft.com...
> > Like Mark replied, it depends on the requirements. In that, I agree;
> > however, where our friend says he starts with User Interface and the
> process,
> > this is where I disagree.
> Just to be clear, I mean that the user requirements drive the database, not
> the "real world." The real world is too big to model. The hard part (that I
> think our other friend needs to better define) is what part of that real
> world is important to his users. Since all the users care about is what they
> see on the screen or on a bit of paper, starting from the user interface is
> usually the best way to determine what part of the real world needs to be
> modelled.
> Take subtitles, for example. If the user requires a screen that lets him
> search for subtitles, then clearly, subtitles need to be in a column of
> their own. If such a search (= screen) isn't required, subtitles could
> simply be included in the title. The real world (alone) doesn't allow one to
> make this choice.
> > I would claim that the Title is an attribute of the Product; moreover, it
> is
> > a candidate key.
> Actually, titles aren't unique (in the real world:).
> > My favorite example is the Middle Initial
> > attribute. Now, in this case, seperating that attribute out to a
> dependent
> > subsidiary table would be overkill.
> But in "the real world," middle initial is a separate piece of information
> from the rest of the name. What defines "overkill" is not the real world,
> but the user requirements.
> I do agree with most of your points, however.
>
>|||"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:8C4043F6-1E5D-4DEA-9DA6-B5E4E911E7AD@.microsoft.com...
> Lastly, all I would say to the "real world" model--a rather too loose
term,
> I agree--is that data modeling is more an intent of data definition;
whereas,
> application design is more intent on process modling. At some point, the
two
> will need to work together, but it must be recognized that the design
goals
> of each are targeted at two different aspects of the same project.
> Oftentimes, these targets are divergent.
Good points.
Subscribe to:
Posts (Atom)