I posted this at ServerFault and was told this would be a better spot for it. I have some experience as a developer but am a complete novice when it comes to SQL administration and database design. I'm converting my company's ordering and quoting process from a excel spreadsheet to something a little more robust using an ASP.NET applic...
My friend is building a product to be used by different independent medical units. The database stores a vast collection of measurements taken at different times, like the temperature, blood pressure, etc... Let us assume these are held in a table called exams with columns temperature, pressure, etc... (as well as id, patient_id and ti...
We have a user table which contains all the users from the company. As an administrator you have the permission to delete user entries. The UI shows all the users with a checkbox per row to indicate if it needs to be deleted. It's quite possible that other tables have foreign key relationship(s) with the user table and hence would preven...
We have a requirement to retreive books in a user defined order (e.g. Assuming there are 3 books - BookA, BookB, BookC), its possible that an end user would like to see this rendered as (BookB, BookA, BookC) or (BookC, BookA, BookB). We are thinking of adding a integer column (e.g. sortColumn) to capture this order. Is this a good opt...
I've got one table containing some sort of items, and two tables (a and b) to which these items belong to. One item refers to either one row in a or one row in b. One a or b can have multiple items. Is there a better design than the following (using Oracle 10)? Any pitfalls to watch out for? id a_id(fk) b_id(fk) 1 1 ...
We're becoming an international company and I have been tasked with designing how we're going to store the different countries' specific data. Such as language, postal code regex, telephone number regex, country calling codes, currency, etc. Currently we have tables like these: Countries, Languages, CountryLanguages, CountryCallingCod...
I tried to create a foreign key on one of my tables, referencing a column of a table in a different schema. Something like that: ALTER TABLE my_schema.my_table ADD ( CONSTRAINT my_fk FOREIGN KEY (my_id) REFERENCES other_schema.other_table(other_id) ) Since I had the necessary grants, this worked fine. Now I wonder if there...
Possible Duplicate: Surrogate Vs. Natural/Business Keys The title says it all. If given two tables, Level1 [ID, Title, Number] Level2 [ID, FKID???, Title, Number] A user of the system understands that Level2 is related to Level1 with based on Level1's Number, my question to you is, would you make the relationship based on the...
I am using Visual Web Developer and Microsoft SQL server. I have a tag table "Entry_Tag" which is as follows: entry_id tag_id I want to make the entry_id and tag_id pairing unique. A particular tag can only be applied to an entry once in the table. I made the two columns a primary key. They are also both foreign keys referencing the id...
I'm designing a small database for a personal project, and one of the tables, call it table C, needs to have a foreign key to one of two tables, call them A and B, differing by entry. What's the best way to implement this? Ideas so far: Create the table with two nullable foreign key fields connecting to the two tables. Possibly with...
So it's a standard in basically every address form out there and I'm questioning why? Address Line 2. It's in every form that asks for address details. It's never actually seemed necessary to me. It requires another field in the database and all the goofy maintenance that goes with it. Every time you use an address, you have to concaten...
In a classifieds website, you have several categories (cars, mc, houses etc). For every category chosen, a hidden div becomes visible and shows additional options the user may specify if he/she wishes. I am creating a db now, and I have read some articles about normalization and making it optimized etc... Here is my layup today CATEGO...
I would like to know what should be a good option to implement a system like that: The system will be use by teachers in a school. They can login and see a list of the pupils that they have in the current class/subject. In this list they could mark the pupils that are not attending the class, and some other options... The system shoul...
Suppose I have a table called Apple_Jobs. It defines jobs coming from the Apple_Server. Now suppose I have another server called Orange_Server Should I modify the existing Apple_Jobs table with an attribute called Origin defining where the job came from - AppleServer or OrangeServer. OR make a separate Orange_Jobs table. With...
I'm using MS Visio to model a database and part of the model contains Transaction categories - the parent table has a transactionId, timestamp, amount, and transactionType. There are three child tables - cheque, bank transfer, and credit card, all related to the parent by transactionId. Is there a specific way this kind of relationship...
Take the following create table statement: create table fruit { count int, name varchar(32), size float } Instead of those specific data types, why not have "string", "number", "boolean" or better yet, not having to specify any data types at all. What are the technical reasons for having such specific data types? (as opposed to...
Hey everyone, Let's say I want to make a database that relates emotions with activities. Would it make sense to make a new table every time i encounter a new emotion and inside that table would by say the ids of all the activities that relate to that emotion each id being its own record, or is there a better way of doing it? Thanks ev...
I am looking at a problem which would involve users uploading lists of records with various field structures into an application. The 2nd part of this would be to also allow the users to specify fields to capture information. This is a step beyond anything ive done up to this point where i would have designed a static RDMS structure mys...
We are currently doing an web application which one of the functionality is to create Events by the user. Those events can be later on deleted by the user or administrator. However the client requires that the event is not really physically deleted from database, but just marked as deleted. User should only see non-deleted events, but ad...
heya, I've previously asked questions for this project, regarding the Django Admin, Inlines, Generics, etc. Thanks again to the people who contributed answers to those. For background, those questions are here: http://stackoverflow.com/questions/1918637/django-designing-model-relationships-admin-interface-and-inline http://stackoverfl...