What is the best way to model the following... Assume I have two objects: Agency and Publisher, and both have a 1-to-n relationship to Employee. This is a true 1-to-n relationship, as each Employee can only work for one Agency or one Publisher. Let's assume further that I cannot introduce a supertype (e.g. Employer) which holds the 1-to...
I'm running Sql Server Management Studio 2008 on a decent machine. Even if it is the only thing open with no other connections to the database, anything that has to do with the Database Diagram or simple schema changes in a designer take up to 10 minutes to complete and SQL Management Studio is unresponsive during that time. The same S...
Hi guys, Does anyone have experience with generating database schema from c# (.net 3.5) domain model? Which of the tools produces the cleanest script? Telerik OpenAccess ORM would have been ok but it doesn't produce clean column names from c# classes if the property fields use .net 3.5 implicit private fields. I just need an initial sc...
We are porting an old application that used a hierarchical database to a relational web app, and are trying to figure out the best way to port configuration switches (Y/N values). Our old system had 256 distinct switches (per client) that were each stored as a bit in one of 8 32-bit data fields. Each client would typically have ~100 ...
I write the code for my own website as an educational/fun exercise. Right now part of the website is a blog (like every other site out there :-/) which supports the usual basic blog features, including commenting on posts. But I only have comments enabled for logged-in users; I want to alter the code to allow anonymous comments - that is...
I don't know if the term SQL designer is correct, I'm looking for a graphic tool where you can do something like: then it can generate the SQL code for the creation of the tables. Any hint? ...
I am currently working on a simple revision system that enables me to store multiple versions of a single file, which works fine so far. Table structure is as follows (obsolete columns removed for the sake of brevity): file_id file_revision file_parent file_name -------------------------------------------------------- 1 ...
Hey guys and gals, I have a field in a table that I will be storing different kinds of data in it, Like: X-Large, Medium, Small....or I might store: 22-March-2009, 1 Year, 2 Years, 3 Years...or 06Months, 12 Months, 1 Year, or I might store: "33-36", "37-40"...and that data is not fixed, i might need in the future to add new categories......
Let's say I have a table of promo codes, and a table of contacts. Each contact can have 1 promo code, and each promo code can only be assigned to 1 contact. How would I employ an assignment program that would select a promo code from the table, and assign it to a contact, without having concurrency issues/collisions. Worst case scenar...
We have been assigned to build a e-health platform for a customer, and in the design process we have arrived to this dilema: We have two options for the data model, the HL7 RIM (reference information model) and another one (still to be designed). Although the RIM has been documented extensively and it seems very complete, I'm not sure ...
Every Customer has a physical address and an optional mailing address. What is your preferred way to model this? Option 1. Customer has foreign key to Address Customer (id, phys_address_id, mail_address_id) Address (id, street, city, etc.) Option 2. Customer has one-to-many relationship to Address, which contains a field...
I need to design tables which stores all the (Attributes) metadata of Files i.e., FileName, Author, Title, CreatedDate ... and Custom Metadata ( Metadata added to files by Users) Before examining the tables we can not say what and how many custom tags have been added on Files. Custom Tags i.i.e, CustUseBy, CustSendBy etc. Now To store ...
What is the best database schema to represent an NCAA mens basketball bracket? Here is a link if you aren't familiar: http://www.cbssports.com/collegebasketball/mayhem/brackets/viewable_men I can see several different ways you could model this data, with a single table, many tables, hard-coded columns, somewhat dynamic ways, etc. You n...
I've recently given it a try (SQL 2008 version) and I find them quite okay. Well, the only problem is that the wizard is not intelligent enough to update database structure without first erasing all data. Is this why these projects are not used in practice? Actually never seen anyone using them or ever mentioning them at all. Also nothi...
I have a simple ecommerce type site that will have users with accounts, orders, and contact/billing information for those users. I just wanted to know the best way to setup these tables from an efficiency and logical point of view. So far I have basically two different entities, users and orders. Users will have basic account information...
I'm attempting to create a contacts application that has two main entities - person and company. A person can have many emails, numbers, and addresses. A company can also have many emails, numbers, and addresses. I'm trying to determine the proper design for this scenario. Option #1 - multiple foreign keys Emails, numbers, and addresses...
The idea that web services are small bits of functionality or data that are bundled together and encapsulated as small, stand-alone entities is pretty clear, and makes good sense. But how do services relate to databases that they use or provide an interface for? For example, when moving from a monolithic, 2-tier architecture with a mass...
Looking for guidance on selecting a database provider for a specific key pattern. The only key field will be a pre-allocated unique sequentially-increasing number. During each day between 50 and 100 thousand items will be added, processed (updated), and then retained for a week or so, after which usually the lowest-numbered records wi...
I create a view with many joins. I want to group the resultset by some of the fields I selected. Does setting indices on these fields increases the performance of the query? I'm using MS SQL Server, but it should be nearly the same for all dbs, shouldn't it? SELECT table1.field1, table1.field2, table2.field1 FROM table1 INNER JOIN tabl...
Somewhat related to my previous question, this is regarding creation patterns to enforce table patterns where 'A' has many 'B' children where 'C' is the table of child 'B's for 'A', but has at least ONE. Currently I have: A (A_ID primary key, B_ID reference key) B (B_ID primary key, etc) C (A_ID reference, B_ID reference) The point i...